Search code examples
mysqlsqlexplain

Does rows in mysql explain mean that the table is not properly indexed?

Statemtent:

SELECT * 
FROM `announcements` 
WHERE `website` = 17 
    AND `visible_date` <= '2014-08-05' 
ORDER BY `visible_date` DESC 
LIMIT 1

I want to get the first announcement having visible_date earler than 2014-08-05. Is there a better way to do it?

Now, if I have an index (website, visible_date) I guess MySQL can get the first row using the index without doing a table scan.

But the rows column in my EXPLAIN show 355 rows instead of 1.

Thanks for your help.

Solution

  • You're right, having an index will improve the performance of your query BUT the EXPLAIN command is showing you the execution plan (as described here: EXPLAIN).

    In order to get your result, MySQL first need to select the rows corresponding to your WHERE clause and then it will return only the first result (LIMIT 1).

    That's the reason why the EXPLAIN is showing every matching results, the LIMIT statement is applied to the results that match your conditions.

    An index is used to find a row more quickly but it wont filter the data of your table, you'll find more info about indexes here: MySQL Indexes

    Hope that this explanation will help you to better understand the notion of index.