Yo! We've upgraded from 3.22 to 3.23 with isam->myisam conversion and everything was ok but this - one particular query became much slower. Here's how it was with 3.22:
mysql> select a.ID,c.name,a.title,a.publishedDate,c.id,count(a.id),k.id,c.parentID from articles a LEFT JOIN comments k ON (a.id=k.objectID and k.status and k.objectTable='articles'),newsCategories c,articleXCategory x where year(a.publishedDate) = 2001 and month(a.publisheddate)=10 and dayofmonth(a.publisheddate)=26 and c.langID=1 and a.id=x.articleID and c.ID=x.categoryID and a.published='true' group by a.id order by c.parentID, c.sortnr, a.publishedDate\G [cut] 95 rows in set (8.04 sec) Running this query second time takes 3.16 seconds. mysql> explain <the query here> +-------+--------+----------------------+------------+---------+-------------+------+------------+ | table | type | possible_keys | key | key_len | ref | rows | |Extra | +-------+--------+----------------------+------------+---------+-------------+------+------------+ | c | ALL | PRIMARY,langID | NULL | NULL | NULL | 160 | |where used | | x | ref | articleID,categoryID | categoryID | 4 | c.ID | 621 | | | | a | eq_ref | PRIMARY,published | PRIMARY | 4 | x.articleID | 1 | |where used | | k | ref | objectID,objectTable | objectID | 4 | a.ID | 37 | | | +-------+--------+----------------------+------------+---------+-------------+------+------------+ 4 rows in set (0.00 sec) mysql> select version(); +-------------+ | version() | +-------------+ | 3.22.29-log | Now 3.23 (with a bit different data with the same structure): mysql> select a.ID,c.name,a.title,a.publishedDate,c.id,count(a.id),k.id,c.parentID from articles a LEFT JOIN comments k ON (a.id=k.objectID and k.status and k.objectTable='articles'),newsCategories c,articleXCategory x where year(a.publishedDate) = 2001 and month(a.publisheddate)=10 and dayofmonth(a.publisheddate)=26 and c.langID=1 and a.id=x.articleID and c.ID=x.categoryID and a.published='true' group by a.id order by c.parentID, c.sortnr, a.publishedDate\G [cut] 84 rows in set (38 min 27.30 sec) mysql> explain <the query here> +-------+--------+----------------------+-------------+---------+-------------+------+---------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | |Extra | +-------+--------+----------------------+-------------+---------+-------------+------+---------------------------------------------+ | c | ALL | PRIMARY,langID | NULL | NULL | NULL | 87 | |where used; Using temporary; Using filesort | | x | ref | articleID,categoryID | categoryID | 4 | c.ID | 564 | | | | a | eq_ref | PRIMARY,published | PRIMARY | 4 | x.articleID | 1 | |where used | | k | ref | objectID,objectTable | objectTable | 1 | const | 26 | | | +-------+--------+----------------------+-------------+---------+-------------+------+---------------------------------------------+ 4 rows in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 3.23.43 | Why the times are soo different - 8 sec vs. 38 mins? Although all mysqld settings are almost the same. Of course, splitting the query into two or three helps, but still... -- Vl --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
