Hello,
Mysql version: 4.0.18
I have a table which looks like this:
+--------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+----------------+
| EntryNo | int(11) | | PRI | NULL | auto_increment |
| RecordCreated | datetime | YES | | NULL | |
| RecordLastModified | timestamp(14) | YES | | NULL | |
| ModifiedBy | varchar(80) | YES | | NULL | |
| UpdateID | int(11) | YES | | NULL | |
| Mark1 | tinyint(4) | | | 0 | |
| RecordActive | tinyint(4) | | | 1 | |
| UserData | text | | | | |
| FieldOrderRef | int(11) | YES | | NULL | |
| Session | int(11) | | MUL | 0 | |
| FormCodeRef | varchar(40) | | MUL | | |
| REMOTE_ADDR | varchar(32) | YES | | NULL | |
| HTTP_REFERER | varchar(255) | YES | | NULL | |
| HTTP_USER_AGENT | varchar(255) | YES | | NULL | |
+--------------------+---------------+------+-----+---------+----------------+
The table picks up data from HTML-forms which are store in other tables. If a form has 3 fields and some visitor fills out all elements in the form and submits, this table get added 3 entries.
mysql> select EntryNo,UserData, FieldOrderRef,Session from fg_user_data where Session = 19591;
+---------+----------------+---------------+---------+
| EntryNo | UserData | FieldOrderRef | Session |
+---------+----------------+---------------+---------+
| 202319 | Test Name | 0 | 19591 |
| 202320 | test@test.com | 1 | 19591 |
| 202321 | this is a test | 2 | 19591 |
+---------+----------------+---------------+---------+
3 rows in set (0.00 sec)
The data must be presented in a normal table with colums representing userdata and rows showing each submission (or Session as is used in the table).
So I need to "aggregate the fields".
SO far, I have had a solution working, but it is rapidly becoming too slow as data is growing. In the old solution I copied all the userdata into a multidimentional array.
But that is too slow, so I want to make if faster by using a Temporary table.
I generate the "Create temporary table"-code through PHP, which gives me a table that looks something like this:
+---------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+----------------+
| EntryNo | int(11) | | PRI | NULL | auto_increment |
| Session | int(11) | YES | MUL | NULL | |
| RecordCreated | timestamp(14) | YES | | NULL | |
| Mark1 | int(11) | YES | | NULL | |
| FieldOrder_0 | text | YES | | NULL | |
| FieldOrder_1 | text | YES | | NULL | |
| FieldOrder_2 | text | YES | | NULL | |
+---------------+---------------+------+-----+---------+----------------+
... where FieldOrder_XX represents one of the fields in the UserData table.
mysql> select * from temp_tbl;
+---------+---------+----------------+-------+--------------+---------------+----------------+
| EntryNo | Session | RecordCreated | Mark1 | FieldOrder_0 | FieldOrder_1 | FieldOrder_2 |
+---------+---------+----------------+-------+--------------+---------------+----------------+
| 1 | 19591 | 20050717004252 | 0 | Test Name | test@test.com | this is a test |
+---------+---------+----------------+-------+--------------+---------------+----------------+
1 row in set (0.01 sec)
Now FINALLY COMES THE QUESTION :-)
I use this code to insert data from the userdata table to the temporary table, but the query is incredible slow when 10+ fields are used and more data needs moving.
The query is:
INSERT INTO temp_tbl
(FieldOrder_0, FieldOrder_1, FieldOrder_2, Session,RecordCreated,Mark1)
(SELECT d0.UserData,d1.UserData,d2.UserData, d1.Session, d1.RecordCreated, d1.Mark1
FROM fg_user_data d0
INNER JOIN fg_user_data d1 ON d0.Session = d1.Session
INNER JOIN fg_user_data d2 ON d1.Session = d2.Session
WHERE d1.FormCodeRef = 'c974382be2e58084b'
AND d1.RecordActive = 1
AND d0.FieldOrderRef = 0
AND d1.FieldOrderRef = 1
AND d2.FieldOrderRef = 2
GROUP BY d1.Session);
A query which inserts 610 rows into the temporary table from 13 different tables takes 20min (!) - Some of our clients have more than 10.000 form-entries so it's just not an option 🙂
So what can I do to optimize the above query ?
All helpful suggestions are welcome!
