Search code examples
mysqlsql

how can I estimate row count in mysql search query?

When you search in gmail for a word gmail say: 1–20 of about 98

that 98 is an estimated value How can I do this with mysql in our search?

I don't want to use COUNT function or SQL_CALC_FOUND_ROWS because I have a 1M row and using a fulltext search. I want to estimate not count!

Solution

  • If count or SQL_CALC_FOUND_ROWS don’t help you have some options :

    1. Ask information_schema tables
    select  table_rows
from    information_schema.tables
where   table_schema = 'database_name'
  and   table_name = 'table_name' ;

    Estimate but very performant.

    1. Externalize counter value in an other table and update it during insert/delete statements (more performant and accurate but adds overhead on write statements)

    2. Cache counter in your application (we can imagine having a kind of background worker that will get "updated" value every 30 mins and update the value in the cache) The service will always use the cache value

    Solution 1 and 3 are about estimates but don't forget the tricky part (count with filtered columns.. so with where clause)... Solution 1 doesn't help anymore, Solution 2 and 3 will require to maintain multiple counter based on some "static filtering" like count() where sexe='M' ...

    Finally with random filter values.. you're stuck.. and you'll need to go with the count option on target table with optimal indexing (or maybe trying by indexing data in a full-text search like Elastic search)