0

Currently I am designing a mysql table structure that will hold a couple of million rows. I want to search in it on datetime field by date year and so on but the date will be entered by user so it may not be full date all the time. The field is indexed. Will it be fast enough to use wildcard on the datetime field. Like so

SELECT `id`, `moment`, `object_uid`, `content` FROM `events` WHERE moment like '2015%';

Will it use the index?

There is an option to determine whether or not the user have entered year or month and year and so on but I prefer to let MySQL do the job.

SQLfiddle

0

It's hard to say if your query will use the index or not because that will be based on what MySQL's optimizer decides is best based on your data distribution and statistics.

In any case, a wildcard at the end is Sargable, so at least the wildcard isn't what's preventing your index usage.
See for example this post by Brent Ozar or this by Rob Farley

On the other hand, if you search for year only my guess would be the query won't be selective enough to use an index, year and month might but that's hard to tell.

See my reply to this question for an explanation: Why is the SQL Server query plan not using indexes in a query almost similar to one that uses indexes?
It's a SQL Server question but the same principle applies.

Since SQL server reads entire pages, and not records, your query (as indicated by statistics) needs to be selective enough for the index to be used, if SQL server estimates that it's going to have to read every page anyway it will go for a table scan.

|improve this answer|||||
  • Wildcard suffix can use index with strings, but this is datetime. I modified data a bit to give 2015 better selectivity (and added the actual index) sqlfiddle.com/#!2/54bf8/1 - checking plans you can see MySQL is not able to optimize the LIKE on datetime the way it does with equivalent BETWEEN. It might be possible to implement automatic transformation of such conditions in the future but seems it is not done now - mariadb.atlassian.net/browse/MDEV-5494 – jkavalik Sep 3 '15 at 19:50
0

You can use multiple ways to get dates.
For example:

By Year:

SELECT id, moment, object_uid, content FROM events WHERE YEAR(moment) = 2015;

By Year and Month:

SELECT id, moment, object_uid, content FROM events WHERE YEAR(moment) = 2015 and MONTH(moment) = 1;


By Date:

SELECT id, moment, object_uid, content FROM events WHERE moment >= '2015-01-01'


By DateTime:

SELECT id, moment, object_uid, content FROM events WHERE moment) >= '2015-01-01 00:00:00';

|improve this answer|||||
  • YEAR() has the same problem as LIKE, can not use index. So performance will be the same (typecasting between datetime and string is negligible compared to full table scan) – jkavalik Sep 4 '15 at 14:39

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.