does MySQL short-circuit?

[GeX]

Got some fairly hefty tables in a DB at work and from time to time (rarely) I need to look through them when something goes a bit wonky.

I have been using;

SELECT * FROM table WHERE cData LIKE '%search_term%' ORDER BY dtCreated DESC;

This is taking an age, so I altered it slightly;

SELECT * FROM table WHERE dtCreated > '2010-12-01 00:00:00' AND cData LIKE '%search_term%' ORDER BY dtCreated DESC;

My thinking would be that it'd short-circuit and speed up a lot. It did not.

Is there a way to get MySQL to short-circuit? Is there a better way of doing what I'm trying to do? I cannot alter the DB structure at this time.

 

[Dj_Jestar]

You could try nesting the select, so you SELECT ... LIKE ... FROM (SELECT ... WHERE dtCreated > ...)

Usually not the fastest way, but may help in this scenario. It may be that it is checking every row with the LIKE and dtCreated, rather than what you expect of only checking the LIKE on those which have dtCreated > [date].

 

[GeX]

hmmm, nothing really in it. 1288 seconds vs 1213 seconds. Both far too slow. Going to try dropping the dtCreated

 

[Dj_Jestar]

IIRC date comparisons aren't the fasted in the world. We've got a similar issue on MSSQL.

 

[rpstewart]

If the appropriate indexes aren't in place then there's no way that query will go any quicker.

In the first example MySQL will need to scan the entire table and read the contents of cData.

In the second example, if dtCreated is not indexed then it still needs to scan the entire table, first checking the date and then reading the cData column.

Dj_Jestar's solution is similar to the second example - if there's no index then MySQL needs to scan to get the subset of rows but then it needs to scan again to do the main body of the query.

Indexing is the only realistic way to speed this up.

 

[GeX]

thanks guys. I'm going to look into adding an index to dtCreated - not sure when that'll be able to happen though!

 

[Beansprout]

If you're searching for phrases you may want to look into fulltext searching, and fixed-length columns - choosing the right data types is crucial.