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.

I'm trying to tune a relatively simple query:

select bt.col1,bt.col2
from bigtable bt
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

The number of matches in bigtable is relatively small compared to the overall size (< 1%)

Here's the explain plan:

+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+
| id   | select_type | table | type   | possible_keys   | key     | key_len | ref             | rows     | Extra       |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+
|    1 | PRIMARY     | bt    | ALL    | ix_smalltable_id| NULL    | NULL    | NULL            | 22709766 | Using where |
|    1 | PRIMARY     | st    | eq_ref | PRIMARY,ix_name | PRIMARY | 2       | bt.smalltable_id|        1 | Using where |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+

Somehow, even though it would be easier to pickup the index, it doesn't.

I then tried to force the index:

select bt.col1,bt.col2
from bigtable bt force index (ix_smalltable_id)
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

But the query plan and query time is the same. It doesn't want to use the index.

I tried doing where bt.smalltable_id in (select ...), but same query plan and time.

But if I fetch the smalltable_id first, and then embed it in the select, it's much faster.

The question

Can I force the index by enabling some flags? Is this a limitation of the query engine?

Table structure

CREATE TABLE `smalltable` (
  `smalltable_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`smalltable_id`),
  KEY `ix_name` (`name`(10))
) ENGINE=InnoDB AUTO_INCREMENT=5698 DEFAULT CHARSET=utf8

CREATE TABLE `bigtable` (
  `bigtable_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  ...
  `smalltable_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`bigtable_id`),
  KEY `ix_smalltable_id` (`smalltable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23167374 DEFAULT CHARSET=utf8
share|improve this question
    
Can you add the tables' structure (with SHOW CREATE TABLE ...)? –  ypercube Dec 12 '12 at 19:36
    
Which version of MariaDB are you using? –  ypercube Dec 12 '12 at 20:12
    
This was a very refreshing question. +1 !!! –  RolandoMySQLDBA Dec 12 '12 at 20:33

3 Answers 3

up vote 4 down vote accepted

I'm pretty sure the problem is the "partial" index:

CREATE TABLE `smalltable` (
  ...
  KEY `ix_name` (`name`(10))
) 

Try running the query after adding an index on the full length of the column:

ALTER TABLE smalltable
    DROP INDEX ix_name
  , ADD INDEX ix_name_full (name) ;
share|improve this answer
    
that partial index is what I was eyeballing too. +1 –  Derek Downey Dec 12 '12 at 20:31
    
I hate those types of indexes. +1 for the good eye. –  RolandoMySQLDBA Dec 12 '12 at 20:31
    
Thanks, this took care of it. –  Mathieu Longtin Dec 12 '12 at 21:16
    
I just noticed that since ix_name is not unique, the index will only be used when there is only one row in smalltable. –  Mathieu Longtin Dec 13 '12 at 15:14
    
The index will be used whether it is unique or not. If the name column has no two identical values, then it would be better to define the index as UNIQUE. –  ypercube Dec 13 '12 at 15:16

What does STRAIGHT_JOIN give you if you rewrite the query:

SELECT bt.col1,bt.col2
FROM smalltable st
STRAIGHT_JOIN bigtable bt ON bt.smalltable_id = st.smalltable_id
WHERE st.name = 'some name occuring only once in st'
LIMIT 10

I'm mostly curious to see if that is a faster plan, or if MySQL optimizer is 'right' to read in the bigtable rows first.

To further answer the question about MariaDB 'ignoring' force index, the problem is there is nothing for it to use when you do FORCE INDEX (ix_smalltable_id) because it will have to do a full table scan of bigtable anyway.

You might try FORCE INDEX (ix_name) (especially in conjunction with changing the partial index to a full index as ypercube suggests) to see if that helps, but if it does anything it will probably be the same as the STRAIGHT_JOIN refactor.

share|improve this answer

I see two things you can do

USE A BETTER INDEX

Instead of forcing an index on bigtable, make the following index on smalltable

ALTER TABLE smalltable ADD INDEX name_smalltable_id_ndx (name,smalltable_id);

Since you posted the table structures, I can now see that smalltable is InnoDB. In light of this, there is no need for any additional index. You do have a name index ix_name.

REFACTOR THE QUERY

Here is your original query

select bt.col1,bt.col2
from bigtable bt
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

Try taking advantage of the ix_name index and running limit 10 before doing the join

select bt.col1,bt.col2
from 
(
    select b.bigtable_id 
    from bigtable b
    join smalltable s on b.smalltable_id = s.smalltable_id
    where s.name = 'some name occuring only once in st'
    limit 10
) st
join bigtable bt on st.bigtable_id = bt.bigtable_id;

I know this is not the same as the original. I don't expect the EXPLAIN plans to be the same.

Please look at the original query and the refactor and compare:

  • Both execute a WHERE clause on smalltable
  • Both have no WHERE clause on bigtable
    • That's the reason the ix_smalltable_id index is not used
    • Query Optimizer saw no WHERE clause and opted for full table scan as expected
  • Both want to display 10 rows
    • Original query picks 10 rows after the join
    • Refactored query picks 10 keys from smalltable before join

The one slight advantage of the refactored query is the use of the ix_name index. Since secondary key in InnoDB has the primary key attached, it can serve to help the subquery in the refactored query.

In some respects, a refactored query could produce a far worse EXPLAIN plan. In this particular case, I dared to suggest refactoring because of the limit 10. I actually tested this scenario in StackOverflow back on May 16, 2011 : http://stackoverflow.com/a/6023217/491757 . In that post, the query had a worse EXPLAIN PLAN but proved faster over time with more data.

Thanks to @ypercube for refactoring my refactored query !!!

share|improve this answer
    
Sorry but your refactoring is not equivalent to the original query. –  ypercube Dec 12 '12 at 19:47
    
already an index on smalltable_id, it's the primary key –  Mathieu Longtin Dec 12 '12 at 19:59
    
OK since the smalltable is InnoDB and you have ix_name, no need for an additional index. –  RolandoMySQLDBA Dec 12 '12 at 20:02
    
@RolandoMySQLDBA: Consider this: all rows of bigtable having the same smalltable_id. Then your query may return all of them (22M rows) while the original will never return more than 10. –  ypercube Dec 12 '12 at 20:10
    
@ypercube OK I think I am starting to get it. In that respect the queries are not same. In fact, I think if the limit 10 were applied on bigtable before the join, then the query would be the same. What do you think ??? (Let me tweek the refactor) –  RolandoMySQLDBA Dec 12 '12 at 20:18

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.