1

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.

user3841653
  • 75
  • 8
  • if the result is more than 1000 records, then show 1000 as count? – juergen d Aug 02 '14 at 20:55
  • The total requires that the database processes all data. For better performance, don't display the total number of pages when browsing, so the database can stop after processing only part of the data. – Andomar Aug 02 '14 at 21:05

2 Answers2

1

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?

Community
  • 1
  • 1
Menelaos
  • 23,508
  • 18
  • 90
  • 155
0

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.

Marcin Koziarz
  • 66
  • 4