1

I'm trying to optimize the performance of some queries in my application.

In one query with multiple joins and a fulltext search I use SQL_CALC_FOUND_ROWS in a first query for pagination.

Unfortunately the performance of the query is very slow, Without the SQL_CALC_FOUND_ROWS the query is about 100 times faster.

I there a possibility to get a better performance in this case?

I tried a single count-query without the SQL_CALC_FOUND_ROWS, but this query is an additional second slower than the SQL_CALC_FOUND_ROWS-query.

user229044
  • 232,980
  • 40
  • 330
  • 338
iparker
  • 51
  • 2

1 Answers1

0

Without knowing anything about your table structure and query we can't possibly tell if the query can be faster.

With SQL_CALC_FOUND_ROWS it first needs to process all records but if you use LIMIT 10 it can stop processing records after the first 10 found records. So there is the performance difference. There is no way of getting the same result faster without using a count or SQL_CALC_FOUND_ROWS.

However most of the time, a query can be optimized in different ways.

If you're looking into fulltext search, consider using sphinx. In my experience it always will outperform MySQL: http://www.sphinxsearch.com/

Nin
  • 2,960
  • 21
  • 30