Efficiently Limit SQL Search to past 3 months

user_1612957 0
Viewed 199

I have a list of records dating back a decade. I am looking for the 25 most recent entries that include a word. The problem comes in when there aren't 25 entries, the query takes a long time to complete, as it goes through every record dating back years.

Doing a query to limit the list seems inefficient, as most queries have far more than 25 results.

I'm struggling to find the most efficient way to go about this.

What I currently am working with:

SELECT pk, title, time_item 
FROM list 
WHERE title LIKE '%word%' AND time_item > UNIX_TIMESTAMP() - 60*60*24*90 
ORDER BY pk DESC
LIMIT 25;
mysql sql
3 Answers

Your query looks good, but you might improve performance by using a range between the start and now:

SELECT pk, title, time_item 
FROM list 
WHERE title LIKE '%word%'
AND time_item between UNIX_TIMESTAMP() - 60*60*24*90 and UNIX_TIMESTAMP()
ORDER BY pk DESC
LIMIT 25

The optimizer may use a range over the index, but not use an open-ended condition.

Of course you should have an index on time_item.

user_2561960

For this query:

SELECT pk, title, time_item 
FROM list 
WHERE title LIKE '%word%' AND time_item > UNIX_TIMESTAMP() - 60*60*24*90 
ORDER BY pk DESC
LIMIT 25;

The only index that could help would be on (time_item) or (time_time, title) (this covers the where clause). Alternatively, if you really have lots and lots of data, you might want to partition by time_item.

You are going to have to search for "word" in all the titles. If this is really a word-based search in text, then you might find that full text search is better at winnowing down the titles.

That said, if the performance issue is that you have lots and lots of data that matches the conditions and all of it needs to be sorted, then there is little that you can do.

user_11440350

I was able to shave about 1/3 of the time by switching the order of the WHERE queries (having the time search come before the title search).

Then I was able to save even more by finding the PRIMARY KEY of an item 90 days ago and using that as the query parameter instead of time_add.

SELECT pk, title, time_item 
FROM articles
WHERE pk > (SELECT MIN(pk) FROM articles WHERE time_item > UNIX_TIMESTAMP() - 60*60*24*90) AND title LIKE '%word%'
ORDER BY pk DESC 
LIMIT 25

These changes, with adding an index on time_item, have cut the query time down to about 1/6th of how long it was taking.

user_16129570
Related