mysql using incorrect index on large table

Question

The Problem

I have a table that's about 2 million rows (at 115 MB) and it's about to be much larger. When running some utility scripts on the table I noticed one of my queries was taking a long time (15+ seconds) when a query that was nearly identical took less than a half second right before. Here are the queries:

Query 1:

SELECT `id` FROM `my_table` WHERE `my_column`='test' ORDER BY `id` LIMIT 28000, 1000
Execution time: 0.204 seconds

Query 2:

SELECT `id` FROM `my_table` WHERE `my_column`='test' ORDER BY `id` LIMIT 29000, 1000
Execution time: 10.203 seconds

Indexing and table info

id is a primary key and my_column is also indexed (although at the moment its cardinality is only 1)

• id is an int
• my_column is a varchar(50)

Queries explained

Query 1: type: index, possible_keys: my_column, key: PRIMARY, key_len: 4, rows: 29,000, Extra: Using where

Query 2: type: range, possible_keys: my_column, key: my_column, key_len: 53, rows: 2,139,123 Extra: Using where; Using filesort

As you can see the 2nd query is using the my_column key and filesort and taking forever, but all I did was increment the limit offset by 1,000.

How I temporarily fixed the problem

1) If I remove the WHERE my_column = 'test' condition the mysql optimizer correctly uses the primary key to sort, but I can't remove this condition because soon enough there will be other values in my_column which I'm going need to filter out for this query.

2) If I use FORCE INDEX (PRIMARY) the mysql optimizer will also use the proper index, but this seems to be sort of a hack.

My question

Why exactly is mysql choosing to use the my_column index instead of the primary key? And is there a better way to handle this either in the table definition, indexes, or my query structure?

Joe Stefanelli · Accepted Answer · 2012-02-08 21:46:07Z

3

I would try creating a composite index on the combination of (my_column, id).

answered Feb 8, 2012 at 21:46

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeff Over a year ago

Sigh...I just love dumb oversight. Thanks for the help

Matt MacLean · Accepted Answer · 2012-02-08 21:47:36Z

0

That is strange. Have you tried adding a composite index?

ALTER TABLE `my_table` ADD INDEX  (id, my_column);

If you are only selecting id and always only using my_column in the where clause this should work good.

answered Feb 8, 2012 at 21:47

Matt MacLean

19.7k7 gold badges52 silver badges54 bronze badges

1 Comment

Joe Stefanelli Over a year ago

You'd want my_column to be the leftmost column of the composite index since it is the one being tested in the WHERE clause.

Neil · Accepted Answer · 2012-02-08 21:52:01Z

0

With your current set up, there are two obvious ways to execute the query.

Retrieve the rows in id order and throw away the ones that fail to match the WHERE clause.
Retrieve the rows that match the WHERE clause and sort them in id order.

Presumably MySQL is guessing which way to use based on how many rows you want.

However, if you create an index on both my_column and id, MySQL can then retrive the rows in my_column, id order, starting at the first row where my_column = 'test'.

Note that in the general case this requires all the conditions in the WHERE clause to be equality and all the columns in the WHERE clause to exist in the index.

answered Feb 8, 2012 at 21:52

Neil

55.5k8 gold badges65 silver badges75 bronze badges

Collectives™ on Stack Overflow

mysql using incorrect index on large table

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related