Hello,
I posted this on the newbie forum a few days ago, but I think that this is really the best place for it...
I'm trying to optimize the following SELECT query and I'm running out of things to try. I've got it down to about 7.5 seconds, but I think it should be able to run in well under 1 second, which is what I need.
SELECT count(r.r_id) FROM r INNER JOIN q ON r.q_id = q.q_id INNER JOIN a ON r.a_id=a.a_id;
I currently use MS Access to analyze survey data and I'm hoping to switch to MySQL, partly because of speed. I'm currently testing MySQL 4.1 on a Windows 2000 multi-function machine. The simple SELECT statement I'm using as a speed test is the basis for many more complicated queries I run (many with around 30 joins, usually using the same tables).
Access runs the above query (on the same machine and in the same environment) in under 2 seconds; more complicated SELECT queries can take up to 13 hours.
I've tried all the tips I could find in the manual, almost all of which worked very well. But I've gotten through all the "try this first" fixes, went on to try some others, and now I'm more or less stuck. I must admit that I am getting close to the give-up point, but I would love to find a solution to this and I'm re-reading
http://dev.mysql.com/doc/mysql/en/mysql-optimization.html to see if there's anything I've missed.
I'm eager to show my MS-dependent colleagues that MySQL is the way to go, but I've already spent a week tweaking this query and Access is still 3 times faster, so if anybody has any ideas, I'd be very grateful!
Full details on the query, tables, indices, and what I've tried already are below. Thanks in advance for any suggestions! I'll re-post if I find anything that gets me anywhere.
Regards,
Dave
-------------------------------------------------------------
The query to optimize:
SELECT count(r.r_id) FROM (r INNER JOIN q ON r.q_id = q.q_id) INNER JOIN a ON r.a_id=a.a_id;
Progress so far (tweak -> resulting query time):
Initial query run -> roughly 20 minutes
Indexed all columns in SELECT statement -> 6 minutes
Ran ANALYZE TABLE on all tables -> 2.5 minutes
Switched all tables from InnoDB to MyISAM (duh!) -> roughly 7.5 seconds
Current results of SELECT, EXPLAIN SELECT, SHOW CREATE TABLE, and SHOW INDEX:
mysql> source c:/mysql_files/testquery.txt;
+---------------+
| count(r.r_id) |
+---------------+
| 299016 |
+---------------+
1 row in set (7.22 sec)
mysql> source c:/mysql_files/explaintestquery.txt;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 249
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: r
type: ref
possible_keys: q_id,a_id
key: a_id
key_len: 5
ref: f05.a.a_id
rows: 1606
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: q
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: f05.r.q_id
rows: 1
Extra: Using index
3 rows in set (0.01 sec)
mysql> SHOW CREATE TABLE r\G
*************************** 1. row ***************************
Table: r
Create Table: CREATE TABLE `r` (
`r_id` int(11) NOT NULL default '0',
`s_id` int(11) default NULL,
`q_id` int(11) default NULL,
`a_id` int(11) default NULL,
`a` text,
`r_type_id` int(11) default NULL,
`adj_before` datetime default NULL,
PRIMARY KEY (`r_id`),
KEY `s_id` (`s_id`),
KEY `q_id` (`q_id`),
KEY `a_id` (`a_id`),
KEY `a` (`a`(10))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW INDEXES FROM r\G
*************************** 1. row ***************************
Table: r
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: r_id
Collation: A
Cardinality: 387048
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: r
Non_unique: 1
Key_name: s_id
Seq_in_index: 1
Column_name: s_id
Collation: A
Cardinality: 4720
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: r
Non_unique: 1
Key_name: q_id
Seq_in_index: 1
Column_name: q_id
Collation: A
Cardinality: 282
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: r
Non_unique: 1
Key_name: a_id
Seq_in_index: 1
Column_name: a_id
Collation: A
Cardinality: 241
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 5. row ***************************
Table: r
Non_unique: 1
Key_name: a
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 32254
Sub_part: 10
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
5 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE q\G
*************************** 1. row ***************************
Table: q
Create Table: CREATE TABLE `q` (
`page_id` text,
`q_id` int(11) NOT NULL default '0',
`name` text,
`title` text,
`q_type_id` int(11) default NULL,
`p_id` int(11) default NULL,
`group_q_id` int(11) default NULL,
`a_q_id` int(11) default NULL,
`c_id` int(11) default NULL,
`q_weight` int(11) default NULL,
`s_weight_q_id` int(11) default NULL,
`d_ent_p` int(11) default NULL,
`adj_p` int(11) default NULL,
`special_q_id` int(11) default NULL,
`special_q_name` text,
`reference_q_id` int(11) default NULL,
PRIMARY KEY (`q_id`),
KEY `reference_q_id` (`reference_q_id`),
KEY `special_q_id` (`special_q_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW INDEXES FROM q\G
*************************** 1. row ***************************
Table: q
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: q_id
Collation: A
Cardinality: 413
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: q
Non_unique: 1
Key_name: reference_q_id
Seq_in_index: 1
Column_name: reference_q_id
Collation: A
Cardinality: 15
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: q
Non_unique: 1
Key_name: special_q_id
Seq_in_index: 1
Column_name: special_q_id
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE a\G
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`a_id` int(11) NOT NULL default '0',
`a` text,
`q_id` int(11) default NULL,
`p_id` int(11) default NULL,
`data_entry_alias` int(11) default NULL,
`special_a_id` int(11) default NULL,
`special_a_name` text,
`results_group_id` int(11) default NULL,
`results_group_name` text,
`results_p_id` int(11) default NULL,
PRIMARY KEY (`a_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW INDEXES FROM a\G
*************************** 1. row ***************************
Table: a
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: a_id
Collation: A
Cardinality: 249
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.00 sec)