Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thanks!


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.

share|improve this question
    
Please post the entire EXPLAIN for all tables for the type of query you are asking about. One table's entry is not sufficient on its own. Also importantly, the primary key is an index... –  Michael - sqlbot Dec 9 '13 at 0:08
    
You know the primary key is an index and I know the primary key is an index, but log_queries_not_using_indexes doesn't appear to know that. Which is one of the reasons why I'm not sure whether this is a real thing. :/ I added the explain for table1, but it's not too informative. –  jdavid Dec 9 '13 at 18:40
    
Also, I've tried backing into this, with the form: SELECT t1.id FROM table2 t2 JOIN table1 t1 ON t1.t2_id = t2.id WHERE t2.b = 2 and an index on table2 ( b, id ), but no joy. :( –  jdavid Dec 9 '13 at 18:40
    
can you provide us with some more information like table stucture show create table [table_name] and index information show index from [table_name]? because based on your information it looks like an INDEX(t2.id, t2.b) or INDEX(t2.b, t2.id) may result in beter index selectivity –  Raymond Nijland Dec 9 '13 at 19:54
    
This behavior appears independent of table structure, so I've added a trivial example to the post. In doing so, I did learn that the execution plan is affected by the number of rows, rather than the table structure. (Detail in post.) –  jdavid Dec 10 '13 at 0:41
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.