Limiting search result items

user_17971222 0
Viewed 103

I want to optimize my paginated search result page

For example I have 100millions post to search. and user just type "a". It will take very long to search all of that because we use SQL_CALC_FOUND_ROWS for pagiation purposes

The fact is that there is no need to search all milions of rows (posts) and the answer "1000+" is enough for users. So we need to stop search after we found 1000 results.

We want to show information like this to user: Showing 1–10 of 1000+ results [RESULTS] Page 1 .... Page 100

How to do this without losing our pagination functionality?

My current query looks maybe something like this:

SELECT SQL_CALC_FOUND_ROWS xxx_posts.ID
FROM xxx_posts
WHERE 1=1
AND (((xxx_posts.post_title LIKE '%a%')
LIMIT 0, 10
mysql
1 Answers

Your particular example is too risky to try to speed up. However, for the general case...

SELECT id
    FROM xxx_posts
    WHERE ...
    LIMIT 1000, 1;

If you get a row, then there are at least 1000 rows.

Do not use GROUP BY or ORDER BY unless an index can handle the WHERE and those clauses. Otherwise, they will require fetching all the rows, then sorting before getting to the LIMIT.

Your particular example is risky... Using LIKE with an initial wildcard cannot use an index. If there are not 1000+ matching rows, it check every title in the entire table without satisfying the LIMIT 1000, 1. Nothing saved!

Can you use a FULLTEXT index?

See also MariaDB's setting to limit an individual statement's execution time.

user_17668310
Related