[drupal-devel] MySQL indices not being picked up
Does anyone know why MySQL does not use indices/keys for the following query despite the fact it has plenty of indices to choose from ... mysql> EXPLAIN SELECT DISTINCT m.*, r.*, u.name, u.uid FROM cvs_messages m INNER JOIN cvs_files f ON m.cid = f.cid INNER JOIN cvs_repositories r ON m.rid = r.rid INNER JOIN users u ON m.uid = u.uid ORDER BY m.created DESC LIMIT 0, 10; +-------+--------+-------------------------------------+--------- +---------+-------+-------+---------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+-------------------------------------+--------- +---------+-------+-------+---------------------------------+ | m | ALL | PRIMARY,rid_created,cid_created,uid | NULL | NULL | NULL | 15732 | Using temporary; Using filesort | | f | ref | cid,cid_2 | cid | 4 | m.cid | 3 | Using index | | r | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where | | u | eq_ref | PRIMARY | PRIMARY | 4 | m.uid | 1 | | +-------+--------+-------------------------------------+--------- +---------+-------+-------+---------------------------------+ mysql> EXPLAIN cvs_messages; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | cid | int(10) unsigned | | PRI | 0 | | | rid | int(10) unsigned | | MUL | 0 | | | created | int(10) unsigned | | MUL | 0 | | | user | varchar(255) | | MUL | | | | message | text | YES | | NULL | | | uid | int(10) unsigned | | MUL | 0 | | +---------+------------------+------+-----+---------+-------+ mysql> EXPLAIN cvs_files; +---------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+-------+ | cid | int(10) unsigned | | MUL | 0 | | | rid | int(10) unsigned | | MUL | 0 | | | file | varchar(255) | | MUL | | | | branch | varchar(255) | | MUL | | | | revision | varchar(255) | | | | | | lines_added | int(10) unsigned | | | 0 | | | lines_removed | int(10) unsigned | | | 0 | | | nid | int(10) unsigned | | MUL | 0 | | | uid | int(10) unsigned | | MUL | 0 | | +---------------+------------------+------+-----+---------+-------+ mysql> EXPLAIN cvs_repositories; +------------+---------------------+------+-----+--------- +----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+--------- +----------------+ | rid | int(10) unsigned | | PRI | NULL | auto_increment | | name | varchar(255) | | UNI | | | | root | varchar(255) | | | | | | modules | varchar(255) | | | | | | diffurl | varchar(255) | | | | | | newurl | varchar(255) | | | | | | trackerurl | varchar(255) | | | | | | method | tinyint(2) unsigned | | | 0 | | | updated | int(11) unsigned | | | 0 | | +------------+---------------------+------+-----+--------- +----------------+ -- Dries Buytaert :: http://www.buytaert.net/
Dries: On Sun, May 15, 2005 at 01:40:30PM +0200, Dries Buytaert wrote:
Does anyone know why MySQL does not use indices/keys for the following query despite the fact it has plenty of indices to choose from ...
Without looking at this too hard and not having the actual table definitions in front of me... I noticed some of the columns used in the joins are part of multi-column indexes. When querying columns that are in a multi-column index, one can't reach a column in the middle without using the preceeding columns. Example: create index foo on bar (one, two, three); If you try to join/select/etc on column "two", the index can't be used. The index only gets used if your query involves the prior columns. The key gets used for these: select * from foo where one = 11 or select * from foo where one = 11, two = 22 or select * from foo where one = 11, two = 22, three = 33 BUT, the index won't get used on these: select * from foo where two = 22 or select * from foo where two = 22, three = 33 or select * from foo where three = 33 So, the order you define the index in is important. One must to take into consideration how the index will be used. If two columns in the index are subjects of frequent queries, but the second column sometimes not used in conjunction with the first, a separate index comprised of that column alone should be considered. I hope my explanation is clear. Perhaps you already knew this. Is that the issue here? --Dan -- T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y data intensive web and database programming http://www.AnalysisAndSolutions.com/ 4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409
Hi Daniel,
So, the order you define the index in is important. One must to take into consideration how the index will be used. If two columns in the index are subjects of frequent queries, but the second column sometimes not used in conjunction with the first, a separate index comprised of that column alone should be considered.
I hope my explanation is clear. Perhaps you already knew this. Is that the issue here?
Turns out that solved part of the problem. I didn't knew the order of muti-column indices was important. Thanks for the clear explanation. If only people like you could help tune Drupal's SQL queries. ;-) -- Dries Buytaert :: http://www.buytaert.net/
On Sun, May 15, 2005 at 08:47:33PM +0200, Dries Buytaert wrote:
Turns out that solved part of the problem. I didn't knew the order of muti-column indices was important. Thanks for the clear explanation.
Glad I could help.
If only people like you could help tune Drupal's SQL queries. ;-)
If only I had more time. :) My volunteer commitments are already too large. --Dan -- T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y data intensive web and database programming http://www.AnalysisAndSolutions.com/ 4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409
participants (2)
-
Daniel Convissor -
Dries Buytaert