I have been monitoring my server and have noticed a few slow queries. One is getting the most recent date in a table with about 2691225 records.

I am using the following:

SELECT dt FROM dly WHERE dt < {today's date} ORDER BY dt DESC LIMIT 1

I need the less than today, as there are some anomaly records in the db that have dates that are greater than today somehow.

New relic gives the following tips:

  • This table was retrieved with a full table scan, which is often quite bad for performance, unless you only retrieve a few rows.
  • No index was used in this part of the query.
  • MySQL had to do an extra pass to retrieve the rows in sorted order, which is a cause of poor performance but sometimes unavoidable.
  • You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
  • Approximately 2691477 rows of this table were scanned.
  • 2
    Do you have an index on the date field? If not, then try adding one, as suggested. – til_b Mar 31 '16 at 12:24
  • Is it ok to have an index on a field that is not unique? – Stephen H Mar 31 '16 at 12:33
  • 2
    Yes. See stackoverflow.com/a/23539771 for more explanation of the terminologies in mysql. – til_b Mar 31 '16 at 13:05
  • 1
    ALTER TABLE dly ADD INDEX(dt); – Rick James Apr 1 '16 at 2:22

Your Answer

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

Browse other questions tagged or ask your own question.