When I run the following querys in MySQL:

SELECT count(*) FROM Inventory AS i WHERE i.lastModified > 1389920146804 


SELECT i._id FROM Inventory AS i WHERE i.lastModified > 1389920146804; 

The EXPLANE command will show that the index on lastModified is used.

But when I run

SELECT * FROM Inventory AS i WHERE i.lastModified > 1389920146804; 

or any other query with more then two rows we got a full table scan.

Why? How can I avoid a full table scan when selecting more then one row?

  • If lastModified it`s not the first column in your multiple select the index will not be used. Create a composite index on the first column,lastmodified to use an index. – Mihai Jan 23 '14 at 22:50
  • What is the storage engine of the Inventory table ??? – RolandoMySQLDBA Jan 23 '14 at 23:07
  • 1
    Please post the table definition and preferably also your explain output so we can verify that you are correctly interpreting them. – Michael - sqlbot Jan 23 '14 at 23:21
  • You can go ahead and try FORCE INDEX. Like I said, I was flying blind. Sorry I couldn't help. Gotta go back to work. Removing my answer ... – RolandoMySQLDBA Jan 24 '14 at 0:28

Your index probably only contains the lastModified column, as well as your _id value as a way of finding the other column values for each row.

So if your predicate (the WHERE clause) isn't filtering your index data down to few enough rows, the effort in looking up the other details over and over is too much and it will decide that it's easier to just scan the whole table.

If your index included all the columns you were interested in, you would see an ordinary seek.

| improve this answer | |
  • When I ran the query in a way that I only got a few hits, the query DB was using the index. Nice! – Jan Seevers Jan 24 '14 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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