Search code examples
mysqlquery-performancedata-migration

MySQL query works on one server but not on another

I am migrating our MySQL Server(5.7.15) to a more powerful dedicated box(5.7.24, data/schema created by a mysqldump from the old server about two weeks ago) and am having to recreate all the server settings that are not preserved by a MySQL dump. I have this query(not written by me):

SELECT * 
FROM   (SELECT t1.job_status, 
               t1.customer_po, 
               t1.customer_name, 
               t1.order_value, 
               t1.code_division, 
               t1.assigned_sales_person, 
               t1.date_assigned, 
               t2.date_order, 
               IF(t1.job_status = 'PENDING', 
               Datediff(Curdate(), t1.date_assigned), 
               Datediff(t2.date_order, t1.date_assigned)) AS days, 
               force4.Code('DV', t1.code_division)        AS code_desc, 
               t2.order_id 
        FROM   customer_po_log AS t1 
               left join orders AS t2 
                      ON t2.po_id = t1.customer_po 
               left join order_item AS t3 
                      ON t3.order_id = t2.order_id 
                         AND t3.code_division = t1.code_division 
        GROUP  BY t1.customer_po) AS u1 
WHERE  u1.job_status = 'PENDING' 
       AND u1.code_division = 'L' 
ORDER  BY u1.days DESC

On the original server, the query completes in .5 seconds. on the new server it overruns the maximum query time. I'm pretty sure the problem stems from the t3 join because of what the "Explain Current Statement" tool is telling me:

enter image description here enter image description here

Is there a setting that is blocking this query from performing the same on the new box? What is the cause of the difference?

Happy to provide any additional necessary information. Thanks!

Solution

  • When you migrate tables from one server to another, always check a couple of things:

    1. Are you certain the tables on the new server have the same indexes as on the old server? Do SHOW CREATE TABLE tbl; to check.

    2. Have you done OPTIMIZE TABLE tbl; for each table after migrating them? You can also do this from the command line with mysqlcheck -o --all-databases

    Optimizing, or analyzing, a table regenerates some internal statistics. Those statistics help MySQL's query planner choose indexes when they help with a query, so avoiding full table or index scans sometimes.