1

This query was working fine when our table records less than 150K but since it is not working fine & very slow - 

SELECT SQL_CALC_FOUND_ROWS main_article.* 
FROM main_articles 
LEFT JOIN main_members 
ON article_mem_id=member_id 
WHERE `article_type`='2' 
ORDER BY article_id 
DESC LIMIT 0,20

How can I improve my query?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
knsmith
  • 91
  • 1
  • 2
  • 12
  • 4
    other than adding indexes to your `where` clause's fields, there's nothing you can do. you're forcing the db to scan the entire table for all possible matches so it can do the found rows calculation. – Marc B Oct 02 '14 at 15:07

1 Answers1

5

There's a good description in this blog: https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

The demonstration shows that yes, using SQL_CALC_FOUND_ROWS is very bad for performance when you use it on a large table.

It is often better to run two queries separately:

/* no need to do the join in this case, because it won't affect the count */
SELECT COUNT(*) 
FROM main_articles 
WHERE `article_type`='2' 

SELECT main_article.* 
FROM main_articles 
LEFT JOIN main_members 
ON article_mem_id=member_id 
WHERE `article_type`='2' 
ORDER BY article_id 
DESC LIMIT 0,20

By the way, this is not related to the SQL_CALC_FOUND_ROWS issue, but I wonder why you are joining to the main_members table. You aren't fetching any columns from it. The LEFT JOIN means that it won't restrict the rows. If I can infer the table relationship from the column names, there can be only one row in main_members for each row in main_articles, so the join won't increase the number of rows either. So there's really no purpose to doing this join.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828