Here's a question that begs to be asked -- When you upgraded from 4.1 to 5.0, did you do an in-place upgrade, or mysqldump your data and then re-import?
MySQL 5.x's query cache and table optimizers work very differently than in 4.1, so the results you are getting are probably from 2 issues: 1) You didn't dump your data first, and only did an inplace upgrade, and; 2) Your system isn't properly optimized for using the query cache. Try using either mysqldump or mysql-administrator to dump out your data to an .sql file. Then re-import all of your data into 5.x. You will see a significant difference in your query speeds once you do this. As to your query cache, make sure that it's on (on by default) and, based on your tables, either your MyISAM key_buffer_size or your InnoDB buffer_pool_size are correct for your server's amount of RAM. HTH! J.R. ---------------------------------------- From: Gmail User <[EMAIL PROTECTED]> Sent: Tuesday, May 22, 2007 2:30 AM To: mysql@lists.mysql.com Subject: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks! I had perfectly working complex queries both with LEFT JOIN and without and they were returning results in under a second. After upgrade to 5.0.x, the same queries would return results in 20-30 second range. Through trial and error, I discovered that in case of SELECT ... FROM table1, table2 ... ORDER BY table2.column will be very slow (10s of seconds), while the same query ordered by table1.column will be in under 1 second. If I switch the order of tables in FROM, the same will hold true in reverse order. Is this a bug or I missed something in my googling? More importantly, what can I do change this--I need those sorts! :-( I got same results with 5.0.22, 5.0.27 (Linux). TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
