Hello.
> data, if may grow, I like the cleanness of the SQL_CALC_FOUND_ROWS, > however, currently, it is only saving me code, not performance. Often it is ok to have more complex code to get better performance. You should decide what is important for you - clearness of the code, or speed of your queries. BTW: ORDER BY with LIMIT clause sometimes can be optimized to be very fast, but don't use SQL_CALC_FOUND_ROWS in you query. See: http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html Scott Haneda wrote: > 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 | > | > +-------+--------+---------------+---------+---------+-----------+-------+-- > --------------+ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
