0

I'm using this query : 

 select field from table1 where time LIKE '%2016-03%' order by time asc limit 1

My problem is that the table has thousands of rows and so it takes too long to search for 1 row. Is it normal?

rsabir
  • 738
  • 1
  • 7
  • 17
xydboom
  • 13
  • 3
  • There are more efficient ways of searching than using LIKE. What is the format of the field you're searching? – andrewsi Mar 23 '16 at 21:21
  • 1
    How about `select field from table1 where month(time) = 3 and year(time) = 2016`? – chris85 Mar 23 '16 at 21:22
  • post the DDL for your table so we can determine if the issue is with the indexing. – Jim Buckley Barret Mar 23 '16 at 21:23
  • 1
    Why can't you say `>= '2016-03-01' and < '2016-04-01'`? – shawnt00 Mar 23 '16 at 21:23
  • @chris85 - I would agree with that solution. – Jim Buckley Barret Mar 23 '16 at 21:24
  • @chris85 Try avoiding functions in queries as much as possible to allow the cache and optimization do the job the best way possible. – Canis Mar 23 '16 at 21:24
  • @Canis Exact comparison should be faster than wildcard matching. – chris85 Mar 23 '16 at 21:29
  • @chris85 That is true for the first time the query runs, but in a setup where the cache can do its job, the functions defeats its purpose as the cache never caches queries with functions due to the result from the function possibly varying between subsequent calls. – Canis Mar 23 '16 at 21:31
  • @chris85 - thats not 100% correct. The Query Cache if not use when you use non-deterministic functions. Rhis Functions are not cached: BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME() DATABASE() ENCRYPT() (one parameter) FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() SLEEP() SYSDATE() UNIX_TIMESTAMP() (no parameters) USER() UUID() UUID_SHORT() See: https://mariadb.com/kb/en/mariadb/query-cache/ – Bernd Buffen Mar 23 '16 at 21:44

3 Answers3

1

LIKE queries are always going to be slower than looking for a specific value.

It'll help immensely to add an INDEX on the field and change your query to LIKE '2016-03%' (there won't ever be anything before the year in a timestamp so drop that first %). It'll be able to take a couple shortcuts, at least.

VoteyDisciple
  • 37,319
  • 5
  • 97
  • 97
  • technically, it's only `like '%...'` and `like '%...%'` that are slow. `like '...%'` can still use string indexes for the portion at the start of the like pattern. – Marc B Mar 23 '16 at 21:31
0

Try adding an INDEX to your time column and as other have pointed out, remove the leading % from the query.

See this post for more info

Community
  • 1
  • 1
Canis
  • 4,130
  • 1
  • 23
  • 27
0

If you use LIKE and starts with % MySQL must make a FULL TABLE SCAN to find the correct Dates, but if you start direct with the year they can use a index : ... KIKE '2016-03-%'

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39