CODEWITHSUNDEEP
mysqlsqlfull-text-search
user3841653
user3841653

Reputation: 85

Faster way to get number of results with a limit of first 1000 records?

In search pages, users have access to first 1000 results. Currently I use following

SELECT COUNT(*) as count FROM (SELECT * FROM jobs WHERE MATCH (title, company, state, city) AGAINST ('senior software engineer san fransisco california') LIMIT 1000) AS a

in order to create pagination.

Is there any faster alternative?

PS: Without any limit, some searches have 10 results, some have 500,000 results.

PS 2: I actually wanted to get exact or rough number of results but it is much slower then the SQL I currently use.

Upvotes: 1

Views: 98

Answers (2)

Menelaos
Menelaos

Reputation: 26549

One way of getting faster results is to upgrade your underlying system.

Below are two of the most popular options that I have seen:

  • sphinx search engine with mysql integration
  • lucene/solr

Sphinx has the added bonus of not affecting your SQL that much but instead adding a new storage engine to mysql in adding to the sphinx damon (if I'm not mistaken).

More information:

Comparison of full text search engine - Lucene, Sphinx, Postgresql, MySQL?

Upvotes: 1

Marcin Koziarz
Marcin Koziarz

Reputation: 76

Try to use keyword SQL_CALC_FOUND_ROWS:

SELECT SQL_CALC_FOUND_ROWS * FROM jobs WHERE MATCH (title, company, state, city) AGAINST ('senior software engineer san fransisco california') LIMIT 1000

Then, retrieve estimated row count using

SELECT FOUND_ROWS();

Although performance of this method is questionable, it may do the job for you.

Also, I'd consider creating additional table containing partial results, being rebuilt from time to time.

Upvotes: 0

Related Questions