如何处理大结果的查询？ （对于搜索栏）

Question

I'm adding a search bar for people to search for other people on my database, but I'm worry that the search will return to much rows. I just want to stop the query, and return none if its to large.

how do I stop the query from continuing if the result is to big(ex.100s of rows)?

Is there a better approach to this problem?

Answer 1

I don't think you can "stop" the query. But if you really want to count the number of rows first you can do it as following:

Let's say your search query looks like this

SELECT *
FROM table_name
WHERE name = 'some name';

You can first count the results

SELECT COUNT(*) AS num
FROM table_name
WHERE name = 'some name';

And then only select the rows (first query) if num <= 100

To combine both queries into one you can use a CROSS JOIN

SELECT *
FROM (
    SELECT COUNT(*) AS num
    FROM table_name
    WHERE name = 'some name'
) num
CROSS JOIN table_name
WHERE num.num <= 100
AND name = 'some name'

If num is > 100 MySQL will stop, because there is nothing to join.

But:

• Its not user friendly - you better use LIMIT and use (or implement) a paging system.
• No guaranty that it would be faster than using LIMIT
• It will be slower in all cases where num <= 100