Slow MySQL JOINS

Portal Home > Knowledgebase > Articles Database > Slow MySQL JOINS

Posted by xocekim, 02-27-2010, 03:53 AM
Hello, I have a Python/PostgreSQL program I am moving to Python/MySQL and am having some speed issues. I have 2 tables both with approximately 16,000 records in and my query is SELECT a.product,a.price,b.price FROM (costs_yesterday a INNER JOIN costs_today b ON a.url = b.url) WHERE b.price < a.price ORDER BY a.product ASC; On my PostgreSQL database this gives results immediately however in MySQL my last query took 1m41s. Both databases have been installed with all defaults. I have tried running MySQL with both my-small.cnf and my-medium.cnf and I am using "psql " and "mysql -u " to run the commands, I am not trying to do this within Python.. yet. Also Im using MySQL MyISAM. Any help would be appreciated, thanks. Last edited by xocekim; 02-27-2010 at 03:58 AM. Reason: Additional information
Posted by mattle, 03-01-2010, 08:29 AM
please post the (MySQL) results of:
Posted by xocekim, 03-02-2010, 02:08 PM
Hi mattle thanks for the reply. I appear to of fixed the problem with an INDEX on the url column. Sorry for newbie mistake Down the 0.3 seconds now.
Posted by TonyB, 03-02-2010, 04:26 PM
Ahh yeah indexes are your friend when you're joining on particular fields frequently. Unfortunately most users do not use them and queries can be a real killer without them Having a good size query cache also helps with joins.
Posted by mattle, 03-02-2010, 05:49 PM
True...but be careful there...it's no substitute for a properly indexed table.
Posted by aradapilot, 03-02-2010, 05:57 PM
why is that join in parenthesis? it should not be.