sql - How do you get a reasonably accurate estimate of the total number of rows in a heavily-filtered query when COUNT without a

admin管理员组

文章数量:1277382

I've got a MySQL database of several million rows which is exposed via a PHP web app. I want to let users explore this data using a wide variety of filters including ones which search in TEXT and VARCHAR columns.

These filtered queries are run with a limit of 100 per page. Even with multiple filters applied the results appear pretty quickly (< 0.1s per 100).

However, I would like to display a count of the total number of rows obtained from the user's selected filters. This could be hundreds of thousands of rows. Displaying them 100 at a time is very fast, but running a COUNT without the LIMIT 100 is extremely slow (up to 30 seconds).

I can't really optimise the data for filtering or use cached result sets because of the sheer number of possible combinations users might select.

Is there a MySQL approach which might let me get an approximation of the total number of filtered rows without a very expensive COUNT?

If not, what's the most user-friendly approach here? I'd prefer to give the users some kind of approximate figure rather than none at all, if that's possible.

I've got a MySQL database of several million rows which is exposed via a PHP web app. I want to let users explore this data using a wide variety of filters including ones which search in TEXT and VARCHAR columns.

These filtered queries are run with a limit of 100 per page. Even with multiple filters applied the results appear pretty quickly (< 0.1s per 100).

However, I would like to display a count of the total number of rows obtained from the user's selected filters. This could be hundreds of thousands of rows. Displaying them 100 at a time is very fast, but running a COUNT without the LIMIT 100 is extremely slow (up to 30 seconds).

I can't really optimise the data for filtering or use cached result sets because of the sheer number of possible combinations users might select.

Is there a MySQL approach which might let me get an approximation of the total number of filtered rows without a very expensive COUNT?

If not, what's the most user-friendly approach here? I'd prefer to give the users some kind of approximate figure rather than none at all, if that's possible.

• sql
• mysql
• query-optimization

Share Improve this question Follow edited Feb 24 at 6:30 Shadow 34.3k1010 gold badges6565 silver badges7474 bronze badges asked Feb 24 at 5:27 WackGetWackGet 2,97544 gold badges4040 silver badges5454 bronze badges 10

• 'SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result set, disregarding any LIMIT clause. The number of rows can then be retrieved with SELECT FOUND_ROWS(). See Section 14.15, “Information Functions”.' - dev.mysql/doc/refman/8.4/en/select.html , perhaps – P.Salmon Commented Feb 24 at 7:03
• 1 @P.Salmon - That function has been removed from MySQL 8.x. Anyway, it was no faster than running the SELECT COUNT(*) .. WHERE blah blah blah under discussion. – Rick James Commented Feb 24 at 7:08
• There is no general solution to your question. However, if you can show us SHOW CREATE TABLE and a sample SELECT COUNT(*)..., perhaps we can devise a not-too-kludgy speedup. – Rick James Commented Feb 24 at 7:09
• While I understand why you asked these two questions in one go, I would ask them in two separate questions. 1) They need two different sort of expertise - one in mysql, the other in web design. 2) Here on SO you are supposed to ask one question per post. – Shadow Commented Feb 24 at 7:52
• It is not possible to properly guess the row count. Let's look at an example: With 10,000 items in your database and a simple where clause `item_name like '%a%' you'll get the first 100 rows. Now, how many more to come do you expect? It can be zero, it can be 9,900 rows. You cannot know unless you used that exact same filter before and remembered the row count. – Thorsten Kettner Commented Feb 24 at 10:02

 |  Show 5 more comments

1 Answer 1

Sorted by: Reset to default 0

When you're searching a mess of text columns (or varchar columns) with wildcard query predicates such as column LIKE '%searchterm%' what you want is very slow. Inherently. Unfortunately.

Using the nonstandard SQL_CALC_FOUND_ROWS extension in MySQL / MariaDB looks tempting. But it is going to perform just as badly as counting the rows

What can you do?

1. Use LIMIT 101 instead of LIMIT 100 and make your UI offer a link saying More... if it gets 101 results. In general, this means you change your user interface so it doesn't make any claims about the total number of rows to be found.
2. Figure out whether you can use FULLTEXT search for the application.
3. Switch to PostgreSQL and use their trigram matching indexes.
4. Use some sort of external text search server.
5. Build your own trigram indexing in MySQL. I did this once to solve a very specific problem. It worked, but it is a big pain in the, umm, neck to develop and maintain.

本文标签：