After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one query pattern I've run across in a couple of different places that I can't seem to figure out how to index. (Or whether I should.)
The basic query looks like this:
SELECT t1.id FROM table1 t1 JOIN table2 t2 ON t1.t2_id = t2.id WHERE t2.b = 2;
Setting up table1 is no problem. According to EXPLAIN, an index on t1.t2_id gets picked up and used, no problem:
id: 1 select_type: SIMPLE table: table1 type: index possible_keys: t2_id key: t2_id key_len: 4 ref: NULL rows: 31449 Extra: Using index
In the various examples I have found, the relationship between table1 and table2 is either one to one or many (t1) to one (t2). t2.b is INT NOT NULL and it's being compared to a constant.
But for table2, EXPLAIN says this query is disregarding indexes and just using the primary key:
id: 1 select_type: SIMPLE table: table2 type: eq_ref possible_keys: PRIMARY,idx_b,idx_id_b key: PRIMARY key_len: 4 ref: db.t1.id rows: 1 Extra: Using where
Where idx_b is on
t2 ( b ) and idx_id_b is on
t2 ( id, b ).
None of the queries where this pattern is evident take particularly long to run, e.g. for two different examples:
Query_time: 0.031354 Lock_time: 0.000041 Rows_sent: 0 Rows_examined: 59806 Query_time: 0.000441 Lock_time: 0.000087 Rows_sent: 0 Rows_examined: 686
So maybe I'm chasing something that isn't worth chasing. But the first one (31ms) runs once per minute, and the second one runs a lot. So if it's possible to optimize these queries, I sure would like to do so.
However, to take the top example, the optimizer is not wrong. With
USE INDEX ( idx_id_b ) the query time jumps to 120ms, and with
USE INDEX ( idx_b ) it jumps (predictably) to 380ms. So if there is an index that would help, it isn't one of these.
There do seem to be some similar questions out there, but unfortunately I didn't turn up any that were answered. Based on some of the comments on those, I tried this:
SELECT t1.id FROM table1 t1 JOIN ( SELECT id FROM table2 WHERE b = 2 ) t2 ON t1.t2_id = t2.id;
And it didn't seem to make any difference. The performance and EXPLAIN seem the same.
Is there a better way to index or structure queries of this type, or am I chasing shadows here?
This occurs all over the place on different tables, so there isn't just one pair of tables. For example, one can trivially reproduce this with:
CREATE TEMPORARY TABLE table1 ( id INT AUTO_INCREMENT PRIMARY KEY, t2_id INT UNIQUE NOT NULL ); CREATE TEMPORARY TABLE table2 ( id INT AUTO_INCREMENT PRIMARY KEY, b INT NOT NULL); CREATE INDEX i_b ON table2 ( b ); CREATE INDEX i_id_b ON table2 ( id, b ); CREATE INDEX i_b_id ON table2 ( b, id ); EXPLAIN SELECT t1.id FROM table1 t1 JOIN table2 t2 ON t1.t2_id = t2.id WHERE t2.b = 2; -- opt1 EXPLAIN SELECT t1.id FROM table2 t2 JOIN table1 t1 ON t2.id = t1.t2_id WHERE t2.b = 2; -- opt2
However, since I have multiple examples, what I found while prepping that example was that MySQL changes the execution strategy of opt2 above based on the number of rows in table2 with b = 2. If there are few, opt2 seems to do the right thing. If there are many, it seems to fall back to just using the primary key. (I.e. it becomes identical to opt1.) As in the production tables the number of rows involved are in the tens of thousands, it doesn't seem to help. So that kind of reinforces the theory that this diagnostic is just being silly about the whole thing.
In at least one case, that prompted me to go ahead and factor out the query.
Edit: Added requested EXPLAIN results for table1.
Edit: Added more info about table structure and query alternatives.