2

I have a table with a large number of records ( > 300,000). The most relevant fields in the table are:

  • CREATE_DATE
  • MOD_DATE

Those are updated every time a record is added or updated.

I now need to query this table to find the date of the record that was modified last. I'm currently using

SELECT mod_date FROM table ORDER BY mod_date DESC LIMIT 1;

But I'm wondering if this is the most efficient way to get the answer.

I've tried adding a where clause to limit the date to the last month, but it looks like that's actually slower (and I need the most recent date, which could be older than the last month).

I've also tried the suggestion I read elsewhere to use:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'db'
   AND TABLE_NAME = 'table';

But since I might be working on a dump of the original that query might result into NULL. And it looks like this is actually slower than the original query.

I can't resort to last_insert_id() because I'm not updating or inserting.

I just want to make sure I have the most efficient query possible.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
hepabolu
  • 1,214
  • 4
  • 18
  • 29

3 Answers3

3

The most efficient way for this query would be to use an index for the column MOD_DATE.

From How MySQL Uses Indexes

8.3.1 How MySQL Uses Indexes Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially.

You can use

SHOW CREATE TABLE UPDATE_TIME;

to get the CREATE statement and see, if an index on MOD_DATE is defined.

To add an Index you can use

CREATE INDEX

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

see http://dev.mysql.com/doc/refman/5.6/en/create-index.html

VMai
  • 10,156
  • 9
  • 25
  • 34
  • Before resorting to indexing, how often do you need to get the last modified date? If it is substantially less frequent that row changes, don't do it. The index will increase the time needed for updates since it has to maintain the index. – Robert Co Jul 06 '14 at 15:12
  • @RobertCo The OP said _I just want to make sure I have the most efficient query possible._ That is the title of this question too. I would clearly go for an index in answer to this question. – VMai Jul 06 '14 at 16:25
  • Just answering a question without understanding why someone is doing what he is doing can cause more harm than good. Developers should broaden their view to not just do as they are told and wash their hands off it, but take into account the consequence of their actions. – Robert Co Jul 06 '14 at 16:40
  • @RobertCo Well I give the OP good credit. Of course an index has its costs. I don't dispute that. – VMai Jul 06 '14 at 16:46
  • To all people answering: the field doesn't have an index. @RobertCo: good point. AFAIK the field is only written to, never directly used in the actual application, so therefore no index. I'm building some maintenance scripts to run directly on the database. For now it's manual, but I'm looking into automating it to run once a day e.g. This would probably mean that adding an index would cause more overhead than the benefit it provides. – hepabolu Jul 09 '14 at 10:01
  • All of you thanks for the answers. All of them are useful, I'm not sure how to indicate that. – hepabolu Jul 09 '14 at 10:04
  • @hepabolu Just vote them up. I'm glad we could help you. – VMai Jul 09 '14 at 10:09
2

Make sure that both of those fields are indexed.

Then I would just run -

select max(mod_date) from table

or create_date, whichever one.

Make sure to create 2 indexes, one on each date field, not a compound index on both.

As for a discussion of the difference between this and using limit, see MIN/MAX vs ORDER BY and LIMIT

Community
  • 1
  • 1
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
1

Use EXPLAIN:

http://dev.mysql.com/doc/refman/5.0/en/explain.html

This tells You how mysql executes statement, thanks to that You can figure out most efficient way, cause it depends on Your db structure and there is no one universal solution.

szymanskilukasz
  • 463
  • 3
  • 13