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
Best Answer
I'm pretty sure the problem is the "partial" index:
Try running the query after adding an index on the full length of the column: