1

When I run the following querys in MySQL:

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

or

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?

Improve this question
4
  • 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
    Commented Jan 23, 2014 at 22:50
  • What is the storage engine of the Inventory table ???
    – RolandoMySQLDBA
    Commented Jan 23, 2014 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
    Commented Jan 23, 2014 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
    Commented Jan 24, 2014 at 0:28

1 Answer 1

Reset to default
1

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
1
  • 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
    Commented Jan 24, 2014 at 10:58

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.