Mysql: Query not working on another PC with the same config

Question

I have a query running fine on my current PC that is my developpement PC but not in production environment.

The two environement have the same material configuration. The setting of the database are the same two.

On dev the query execute in less than 30 sec, but when I try it on production even after 2 hours I don't get result.

This is the only running query on the server at the moment of execution.

Do you have any idea of why this is happening ?

SELECT td.td_date,
       td.td_number,
       td.td_status,
       td.td_open_datetime,
       td.td_update_datetime,
       Min(ack.ackup) ackUpdate
FROM   t_ticketdossier_td td
       INNER JOIN (SELECT a.td_number,
                          a.td_update_datetime ackUp
                   FROM   t_ticketdossier_td a
                   WHERE  a.td_status = 'Acknowledged') ack
               ON td.td_number = ack.td_number
                  AND td.td_update_datetime <= ack.ackup
WHERE  td.td_number IN (SELECT td_number
                        FROM   t_ticketdossier_td base
                        WHERE  Date(base.td_date) = @date
                               AND base.td_status = 'Acknowledged'
                               AND base.td_scope IS NULL
                               AND base.td_subcategory NOT LIKE '%RDV%'
                               AND base.td_product_type NOT LIKE '%RDV%'
                        GROUP  BY td_number)
       AND td.td_status = 'Affected'
GROUP  BY td.td_date,
          td.td_number,
          td.td_status,
          td.td_update_datetime

Explain from the dev server.

Explain from the prod server.

Sorry for the links, I don't have enougth reputation to post the image directly.

Cheers, Maxime.

I suspect the production server has a lot more data than your development pc. — Benjamin Diele, Jul 30 '14 at 09:13
PLs check that all indexes of ur dev. environment are created on production as well. — AK47, Jul 30 '14 at 09:14
The data in the production server and dev server are the same. All the indexes are the same too. I made a dump of the production and import it on my dev server. — Maxime Mangel, Jul 30 '14 at 09:16
@MaximeMangel When you compare the `EXPLAIN` statement on both machines, do they differ? — Benjamin Diele, Jul 30 '14 at 09:17
I can't really compare the `EXPLAIN` statement as on my production i can't let my query finish. — Maxime Mangel, Jul 30 '14 at 09:24
@MaximeMangel An `EXPLAIN` query does not execute the query. It analyzes the query and shows you how many rows and what indices it uses should it run the query. — Benjamin Diele, Jul 30 '14 at 09:25
@BenjaminDiele Hum, yes sorry. i will add them to the first message. — Maxime Mangel, Jul 30 '14 at 09:29

Benjamin Diele · Accepted Answer · 2014-07-30T09:44:08.013

1

From what I see in the EXPLAIN statements, it seems that your production database misses quite a few indexes.

For example: TD_STATUS_IDX,TD_NUMBER_IDX

Try adding these and run the EXPLAIN again.

edited Jul 30 '14 at 09:44

answered Jul 30 '14 at 09:36

Benjamin Diele

1,177
1
10
26

Yes, it's seems logic. But when i do a `SHOW INDEXES FROM t_ticketdossier_td`, I get the same output there are just the cardinality which differs. – Maxime Mangel Jul 30 '14 at 09:46
Is their any way to rebuild the index ? Or force their usage ? – Maxime Mangel Jul 30 '14 at 14:21
@MaximeMangel You might want to try [forcing it to use your index](http://stackoverflow.com/a/5719483/1653998). – Benjamin Diele Jul 30 '14 at 14:44
I try, to use force index by specifing TD_NUMBER_IDX as index. [And I got this](http://hpics.li/07bbe90). But as you can see, this index is use for table td... Why don't he wants to use it even by force ? – Maxime Mangel Jul 31 '14 at 14:15

Mysql: Query not working on another PC with the same config

1 Answers1