Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
mysqlcheck -h$host -u$user -p$pass --analyze $dbname I wish that was the case! I tried analyze table ... and optimize table ..., which I presume would be the same. It did not help. I also ran mysqlcheck just to see if it will make a difference. Nope! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
Gmail User schrieb: 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. possible you had set up some query cache in 4, but not currently in 5? how did you 'upgraded' your data? 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. what means this exactly? in reverse ordered tables, query is fast on second or on first table order? Is this a bug or I missed something in my googling? More importantly, what can I do change this--I need those sorts! :-( did your tried an EXPLAIN? -- Sebastian Mendel www.sebastianmendel.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
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]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
possible you had set up some query cache in 4, but not currently in 5? may not be optimized, but yes, query cache is enabled, all 25 MB of it. :-) how did you 'upgraded' your data? regrettably, in-place. interestingly, I was recovering after server crash that chopped of a table. after upgrading the server (in-place), I re-read the corrupt table from script dumped by mysqlbinlog. it is THAT table that is causing me grief. I thought it was some missing indices, but I have indices on all columns I use in WHERE. what means this exactly? in reverse ordered tables, query is fast on second or on first table order? 'select ... from table1, table2, table3 ... order by table1.column' is FAST 'select ... from table1, table2, table3 ... order by table2.column' is SLOW did your tried an EXPLAIN? yes, thanks for reminding me to use it. I compared the two; the slow one uses temporary table and filesort; the fast one does not. Both use where and all select types are SIMPLE. So, with your help, I know why it is slow. Is there a way to out of this without downgrading the server? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
When you upgraded from 4.1 to 5.0, did you do an in-place upgrade, or mysqldump your data and then re-import? As replied to Sebastian's post, in-place. 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. Will this still hold true, even if I dump data out of MySQL 5 and re-import it, or do I need to downgrade first? 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. I guess I will have to check if 8MB is good on 500MB RAM. I did some research back when messing with 4.1, so a good time to do it again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
Gmail User wrote: 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. I had a similar problem once (not related to 4.x-5.x though), it turns out after some maintenance mysql had lost the stats for the table and was doing a terrible job in optimizing queries. A quick analyze of all the tables in the db fixed the problem. mysqlcheck -h$host -u$user -p$pass --analyze $dbname ds 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]
