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))

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`)
  • Can you add the tables' structure (with SHOW CREATE TABLE ...)? Dec 12, 2012 at 19:36
  • Which version of MariaDB are you using? Dec 12, 2012 at 20:12
  • This was a very refreshing question. +1 !!! Dec 12, 2012 at 20:33
  • I'm going to piggy-back on this question because it is currently the top search hit for mariadb ignoring "force index" - if you are a total SQL noob like me, all my EXPLAIN calls were saying that my possible_keys was the indexed field as expected, but even with FORCE INDEX the used key was NULL. My problem was that the query was using an integer but the field was stored as a char string. 😅 Once I added the quotes around FIELD='VALUE' my five-minute query became the expected 2s. Apr 29 at 20:59

3 Answers 3


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) ;
  • that partial index is what I was eyeballing too. +1 Dec 12, 2012 at 20:31
  • I hate those types of indexes. +1 for the good eye. Dec 12, 2012 at 20:31
  • I just noticed that since ix_name is not unique, the index will only be used when there is only one row in smalltable. Dec 13, 2012 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. Dec 13, 2012 at 15:16
  • The ix_name will be, but not the bigtable.ix_smalltable_id. I have a few case where 25 entries were created in smalltable with identical name, and if you use those, you end up with full table scan. Writing a deduplicating job now. Dec 13, 2012 at 15:22

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'

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.


I see two things you can do


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.


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
    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 : https://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 !!!

  • Sorry but your refactoring is not equivalent to the original query. Dec 12, 2012 at 19:47
  • already an index on smalltable_id, it's the primary key Dec 12, 2012 at 19:59
  • OK since the smalltable is InnoDB and you have ix_name, no need for an additional index. Dec 12, 2012 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. Dec 12, 2012 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) Dec 12, 2012 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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