Search code examples
mysqlrackspace

Moving db to remote server slows down join queries

We just moved our db off the actual server box to a mysql 5.6 rackspace cloud instance (on the same net as the server)

In general this had a very positive performance effect.

However this query has slowed down from milliseconds to about 10 minutes...

SELECT COUNT(*)
FROM `orders`
LEFT JOIN jobs
  ON jobs.order_id = orders.id
  AND (original_job_id IS NULL)
WHERE `orders`.`administrator_id` = 2
  AND (orders.order_billing_time > '2001-01-01 06:00:00')`

The jobs table is very large, but similar and much more complex queries on the job table with joins, etc, are running faster.

Solution

  • Is the amount of memory the same size on the new server? If the jobs table is that large you may be running out and using swap space instead. Try increasing the memory size of the database instance.