4.0.18-standard-log Having some performance issues, and I can not see why:
SELECT SQL_CALC_FOUND_ROWS l.id, u.b_first_name FROM logbook AS l LEFT JOIN users AS u ON l.user_id = u.id ORDER BY id DESC LIMIT 0, 20; 20 rows in set (1.21 sec) If I take out the SQL_CALC_FOUND_ROWS, of course, it is instant, as I only get 20 records, however, I added it in to save a select: SELECT count(*) FROM logbook AS l LEFT JOIN users AS u ON l.user_id = u.id; 1 row in set (0.11 sec) So, while the first using SQL_CALC_FOUND_ROWS saves me a second select to get the data, is is significantly slower. While this is a small set of data, if may grow, I like the cleanness of the SQL_CALC_FOUND_ROWS, however, currently, it is only saving me code, not performance. mysql> EXPLAIN SELECT count(*) FROM logbook AS l LEFT JOIN users AS u ON l.user_id = u.id; +-------+--------+---------------+---------+---------+-----------+-------+-- -----------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+-----------+-------+-- -----------+ | l | ALL | NULL | NULL | NULL | NULL | 12671 | | | u | eq_ref | PRIMARY | PRIMARY | 4 | l.user_id | 1 | Using index | +-------+--------+---------------+---------+---------+-----------+-------+-- -----------+ mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS l.id, u.b_first_name FROM logbook AS l LEFT JOIN users AS u ON l.user_id = u.id ORDER BY id DESC LIMIT 0, 20; +-------+--------+---------------+---------+---------+-----------+-------+-- --------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+-----------+-------+-- --------------+ | l | ALL | NULL | NULL | NULL | NULL | 12671 | Using filesort | | u | eq_ref | PRIMARY | PRIMARY | 4 | l.user_id | 1 | | +-------+--------+---------------+---------+---------+-----------+-------+-- --------------+ -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 <http://www.newgeo.com> Fax: 313.557.5052 <[EMAIL PROTECTED]> Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
