Mysql Index is not being used correctly

Question

I got a strange problem with a simple MySQL index.

I got the following table:

CREATE TABLE `import` ( `import_id` int(11) NOT NULL AUTO_INCREMENT, `import_title` text, `import_url` text, `import_description` text, `import_completed` enum('y','n') NOT NULL DEFAULT 'n', `import_user` int(11) DEFAULT NULL, `import_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`import_id`), KEY `import_added` (`import_added`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

In that table are a few thousand rows and I want to get the first 100 with the following Select Clause:

SELECT import_id, import_title, import_url, import_description, import_user, import_added FROM import WHERE import_completed = 'n' ORDER by import_added ASC LIMIT 0,100

When I use DESCRIBE to take a closer look at the query, my local machine uses the "added" index and returns 100 rows within a few milliseconds like expected.

When I use the same database with the same structure and content on my "production" server and use DESCRIBE there, it shows that the index is used as well, but it returns all rows in that table and the query takes about 6 seconds.

What am I missing? Why is the index not working correctly?

ajreal · Accepted Answer · 2010-12-20 12:37:36Z

2

Wrong usage of index

... KEY `import_added` (import_completed, import_added) ...

And do a query execution plan

desc extended SELECT import_id, import_title, import_url, import_description, import_user, import_added FROM import WHERE import_completed = 'n' ORDER by import_added ASC LIMIT 0,100;

answered Dec 20, 2010 at 12:37

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

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

3 Comments

Bastian Over a year ago

Wow, that was fast! Thanks a lot! I must say I feel pretty stupid right now :)

ajreal Over a year ago

@Bastian - more mistake you make, more experience you gained

Konerak Over a year ago

Your localmachine probably used the old import_added key because there were few rows.

cristian · Accepted Answer · 2010-12-20 12:39:56Z

Try to make an index on (import_completed, import_added) The where part will use import_completed and for order by it will use import_added. (I hope this works on MyIsam - On Innodb this is the behavior)

Collectives™ on Stack Overflow

Mysql Index is not being used correctly

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related