MySQL SELECT COUNT(1) statement too slow on table with millions of rows-CodePudding

The following statement takes 0.73 seconds and generates 54,000 results:

SELECT COUNT(1) FROM `table` WHERE MATCH(tagline, location, country) AGAINST('  Germany' IN BOOLEAN MODE)

This statement takes 28 seconds and generates 3.3 millions results:

SELECT COUNT(1) FROM `table` WHERE MATCH(tagline, location, country) AGAINST('  United  States' IN BOOLEAN MODE)

Is there a way to limit the query time by somehow capping the results at one million?

Something like this:

COUNT THIS -->    
SELECT * FROM `table` WHERE MATCH(tagline, location, country) AGAINST('  United  States' IN BOOLEAN MODE)
LIMIT 1000000

CodePudding user response：

Answering my own question:

Wrap your select query in this:

SELECT COUNT(1) FROM ( ... ) as someRandomWord

My select query is altered to this so that results can be limited:

SELECT counter FROM `table` WHERE MATCH(tagline, location, experience, country) AGAINST('  United  States' IN BOOLEAN MODE) limit 1000000

Put this two together:

SELECT COUNT(1) FROM (
SELECT counter FROM `table` WHERE MATCH(tagline, location, experience, country) AGAINST('  business' IN BOOLEAN MODE) limit 1000000
) as someRandomWord

"Germany" --> 54,000 results (0.76 seconds)

"United States" ---> "More than one million results" (2.4 seconds)

CodePudding user response：

You should run explain plan and find your inefficiencies. You can see how to analyze explain plain at this article:

https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html

Based on your SQL it appears your using mysql.

The end result might be to add indexs. This article should help if you need more details about why adding indexs are useful:

https://www.tutorialspoint.com/mysql/mysql-indexes.htm