Query Speed on Mysql 5.5 (Ubuntu) vs Mysql 5.1 (Windows 7) - Drastic Difference

Asked Sep 17 '13 at 20:12

Active Sep 17 '13 at 20:12

Viewed 380 times

I have two IDENTICAL databases:

Database 1: Version: 5.1.68-community OS: Windows 7 Professional (64 Bit)

Database 2: Version: 5.5.32-0ubuntu0.12.04.1 OS: Ubuntu Server 12.04 (64 Bit)

DB Size (both DBs): 25 MB

Querying DB 1: Query - call ledger('2013-08-19','%','%','%','%','%'); Response: 72 row(s) returned Duration: 0.452 sec / 0.000 sec

Querying DB 2: Query - call ledger('2013-08-19','%','%','%','%','%'); Response: 72 row(s) returned Duration: 25.959 sec / 0.000 sec

Running queries outside the procedure yields very similar results.

What could be causing such drastic difference? 0.452 vs 25.959.. Difference in performance is Huge.

Thanks

asked Sep 17 '13 at 20:12

user1431213

4

Out of curiosity, would you be able to run the same tests with MariaDB? https://mariadb.org/ – Homer6 Sep 17 '13 at 20:14
1

Also, what happens when you run MySQL 5.1 on Ubuntu? – Homer6 Sep 17 '13 at 20:15
2

Is there a difference in hardware? – ngneema Sep 17 '13 at 20:18
Thanks for the follow up guys. @ngneema hardware is almost identical. Windows has twice as much RAM but ubuntu is a dedicated production server that runs only Apache and mysql. – user1431213 Sep 17 '13 at 20:27
@homer6 I hound this link: http://stackoverflow.com/questions/17255238/mysql-is-extremely-slow-on-ec2 – user1431213 Sep 17 '13 at 20:27
looks like sub queries are causing the issue. removed 6 sub-queries and performance is almost identical – user1431213 Sep 17 '13 at 20:28
Will try installing 5.1 on Virtual Box to compare – user1431213 Sep 17 '13 at 20:32
@user1431213 Good find. I guess it was the subquery performance of 5.5... did using 5.6 improve it (like the other post)? – Homer6 Sep 17 '13 at 20:36
@Homer6 did not have chance to test yet. Will let you know. Thanks! – user1431213 Sep 17 '13 at 20:45
Can you post the code of the `ledger()` procedure? – ypercubeᵀᴹ Sep 17 '13 at 20:49
@ypercube http://s8.postimg.org/cwuuodsg5/image.png http://s15.postimg.org/6w08e1pyz/image.png – user1431213 Sep 17 '13 at 21:20
Case is resolved! Apparently Trim Function in Where Clause was causing all the "issues". No clue why, but I removed it and performance improved by nearly 1000%. Thank you everyone for your help. – user1431213 Sep 19 '13 at 19:42

Query Speed on Mysql 5.5 (Ubuntu) vs Mysql 5.1 (Windows 7) - Drastic Difference

0 Answers0