ansaurus

Question

MySQL Query performance - huge difference in time

Answer 1

+1 A:

Update your statistics. one time I have a similar issue, and this works for me.

MaK 2010-03-30 22:33:50

If you mean ANALYZE TABLE then I've had that before too and I've done that for all of the tables involved with the query on both databases but no change.

Damo 2010-03-30 23:09:55

Answer 2

+2 A:

Haven't touched MySQL for a while but I am guessing the problem has to do with either

Have you checked you key/join field lengths (actual data) maybe causing the sort_buffer to page to disk joining on large (size) keys? (this smells like a data issue...)
The server settings, basically it is writting to an in memory temp table. I had something similar a few years back. Have you enlarged the key_buffer_size, table_cache, read_rnd_buffer_size, sort_buffer, read_buffer_size to see if that helps?

AJ 2010-03-31 00:50:56

+1 - you got me thinking about this. Updated question with some interesting info on the keys

Damo 2010-03-31 01:20:23

smells like data issue to me too. Tried reducing the size of the keys and also increasing buffers but nothing has worked so far.

Damo 2010-03-31 02:57:25

Answer 3

+1 A:

Since it's InnoDB it looks like it's uite possibly a locking problem. What else is going on at the same time?

le dorfier 2010-03-31 03:03:29

Nothing at all. Both databases have been pulled back to my dev machine.

Damo 2010-03-31 04:49:32

Answer 4

+4 A:

Try adding STRAIGHT_JOIN to the query to see if the execution plan is the issue. The optimizer is choosing a different execution plan for each database and this might be causing the issue.

SELECT STRAIGHT_JOIN * FROM po_shipment ps 
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
JOIN po_shipment_item psi USING (ship_id) 
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items 
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;

UPDATE
1. The new execution plan for database A doesn't need a filesort or temporary table because it's using the primary key. I would start adding USE INDEX into the query to see if you can speed up the query on database B. Is ship_id the primary key of po_shipment? if so, you need to figure out what costs more the group by or filtering of the dates.

SELECT STRAIGHT_JOIN * FROM po_shipment ps USE INDEX( PRIMARY )
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
JOIN po_shipment_item psi USING (ship_id) 
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items 
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;

If that doesn't help try suggesting more of the indexes used in the execution plan of database A for database B.

mholzmann 2010-03-31 03:35:57

Thanks, interesting option. Hasn't changed the end result. Database B is still hanging. The query plans have changed though - both still different. I've updated the question.

Damo 2010-03-31 04:56:59

The reason it dropped the filesort and temporary table on database A is because it's using the primary key for ps. Try adding USE INDEX, i'll add queries in a minute.

mholzmann 2010-03-31 12:09:41

Answer 5

+2 A:

If it's a data problem, I can't tell you what the exact problem is, but here's my favorite strategy to solve this kind of problem:

Try to remove half of your joins. Repeat recursively, until the query runs fast. Then add half of the joins you removed in the last step... (This strategy will require much fewer steps than removing and adding join by join.)

Once you've found out the "bad" join, you can try restricting its values with an additional "where" clause, until the query runs fast again... In each step, always try to reduce the problem in half.

Note: It can very well be the case, that you get a lot more records for the intermediate results of your joins, even if the total amount of data is smaller in database B.

Chris Lercher 2010-04-06 17:06:28

I've done this. See the "Points to Note" in my question. Removing joins on EVENT_TABLE cause it to speed up with each one removed.

Damo 2010-04-07 21:57:55

@Damo: If *every* additional EVENT_TABLE join causes additional time, then the main problem is most likely located in one of the other joins! The EVENT_TABLEs then just multiply the time it takes.

Chris Lercher 2010-04-07 22:45:54

@Chris_I I've updated the question. I can remove all of the other joins leaving only the EVENT_TABLEs and I get the same effect.

Damo 2010-04-07 23:22:39

@Damo: How large is po_shipment itself (in database A and B)?

Chris Lercher 2010-04-07 23:39:35

@Damo: I see, you already answered this: 1776 vs 463. That's not a very big difference. Maybe it's still enough to exhaust some temporary space, if the rows are quite large? I've seen (too) much of this kind of behavior with MaxDB - way fewer problems with Postgres by the way.

Chris Lercher 2010-04-07 23:47:04

@Chris_I the rows aren't particularly large as you can see from the SHOW CREATE TABLE. I can't help thinking that this is something that can be fixed by a variable change to allow for the obviously wonky data.

Damo 2010-04-07 23:52:19

@Damo: I personally would still try deleting *half* of the data in (a copy of) po_shipment etc. This kind of exponential zooming in to the problem has solved basically every data related database problem for me so far, because it always takes only a few steps to find the responsible rows (e.g. even for 4 billion rows, it takes only about 32 steps). HTH.

Chris Lercher 2010-04-08 00:06:10

Answer 6

+1 A:

Since it seems like some issue with the data, it would probably be helpful to figure out what data is causing the problem. Make a third database C and insert half of the data from database B twice(so you have the same number of rows). If database C is slower, than the bad data is there, otherwise it is in the other half. Repeat with smaller and smaller chunk sizes to help find the problem data.

Even though database B is smaller that database A, the tables 'po_header' and 'EVENT_TABLE' are not proportionally smaller. That may have something to do with the speed difference.

Echo 2010-04-06 17:45:36

+1 Good approach

Damo 2010-04-06 21:31:26

Answer 7

A:

I think you're going at this the wrong way. When you do a LEFT JOIN, you're getting back all of the records in EVENT_TABLE regardless of a match to po_shipment.

And you've run a LEFT JOIN to EVENT_TABLE six times. You're getting back (6 * (SELECT count(*) FROM EVENT_TABLE) ) records with every query. Since db A has less records than db B, of course the query runs faster on A.

I think something like this would perform worlds better:

DECLARE @TEMP_EVENT_TYPES table 
(
    EVENT_TYPE varchar(10) PRIMARY KEY
)
;
/*
INSERT VALUES 'MAS0', 'MAS1', 'MAS2', 'MAS3', 'MAS4', 'MAS5'
*/

;
SELECT * FROM po_shipment ps 
JOIN po_shipment_item psi USING (ship_id) 
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items 
JOIN po_header ph ON pa.hdr_id = ph.hdr_id 
INNER JOIN 
    EVENT_TABLE et ON et.TABLE_ID1 = ps.ship_id
INNER JOIN 
    @TEMP_EVENT_TYPES tet ON tet.event_type = et.EVENT_TYPE
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id

iKnowKungFoo 2010-04-06 18:18:41

Wouldn't that only be true when LEFT JOINing from EVENT_TABLE not to EVENT_TABLE. The way he is doing it, LEFT JOINing to EVENT_TABLE, just means that if the join fails then he still gets a result for that row in po_shipment.

Echo 2010-04-06 19:20:32

@Echo: exactly. I still need the row even if it doesn't have that EVENT_TYPE.

Damo 2010-04-06 21:32:27

Answer 8

+1 A:

Have to tried doing "CHECK TABLE" and/or "OPTIMIZE TABLE"?

I had a similar situation where the problem was that the key indexes where not updated properly and doing CHECK TABLE fixed them. A long shot, but worth the try.

Nir Levy 2010-04-07 09:53:53

Thanks. Ran both CHECK TABLE and OPTIMIZE on all tables involved in the query. No effect.

Damo 2010-04-07 22:27:36

Answer 9

+1 A:

To begin with make sure that you have created indexes on the appropriate fields. I am sure you've done that.

Next, try using index hints (USE INDEX) to force the database to use the indexes properly.

I had a similar problem in which I assumed that the indexes were setup and used by mysql properly, but they were not. I was able to work around it using index hints.

Salman A 2010-04-07 10:20:03

Answer 10

+1 A:

What is the selectivity of data in po_shipment.eta and po_shipment.ship_id. Could you post the results of the following query from both databases:

SELECT
    count(distinct eta)/count(*) as eta_selectivity,
    count(distinct ship_id)/count(*) as ship_id_selectivity
FROM po_shipment;

Usually the more selective are the field data (closer to 1), the better index is working. The cause might also be very uneven distribution of data in po_shipment.eta (when you use '2099-01-01' or the like for as a special value) if the optimizer lacks necessary statistics.

By the way, could you provide SHOW CREATE TABLE po_shipment? Indices on the table could shed some light.

UPD: When the selectivity on the field is so low as for eta field, the index is basically useless. Even worse, it might confuse the optimizer on selects and slow down inserts and updates of the data.

The first suggestion is to try removing the index on the eta field and measure the results. It might be that for the A database optimizer does not even try to use the index because of the the very low selectivity and for the B database it gives the index a try.

The second thing that bothers me is why do you group by ship_id in the first place? When there is a need in an implicit temporary table and among the fields there is a TEXT (as in your case) or BLOB one, MySQL will always use on disk temporary tables for sorting (which is implicitly required in GROUP BY). In your case, ship_id is the clustered primary key and the results will anyway be sorted by ship_id. What you need is to extract a base query (already grouped by ship_id if there can be several correscpondences) and JOIN po_shipment with the base query applying your range condition and not using the group by at the top.

And the third. Do you realy need all the fields when you use * at the top? Having joined 10 tables, you receive a lot of fieds. I hardly believe you need al of them. Even exclusion of the TEXT field from the results might boost the query performance.

newtover 2010-04-07 19:37:28

+1 interesting info. I've added the data requested.

Damo 2010-04-07 22:09:46

It's a stripped down query to illustrate the problem. The "real" query is actually a Hibernate query with many more joins and a set of specific fields rather than *. Having said that I can probably drop the GROUP BY. I'll check the results.

Damo 2010-04-08 20:48:50

Answer 11

+1 A:

I've experienced the same when using range selectors less than < and greater than > too.

Experiment: If the range is not too big, have you tried tried to expand the range into and an IN (...,...,...) statement instad ?

E.g

SELECT * FROM po_shipment ps USE INDEX (IX_ETA_DATE)
JOIN po_shipment_item psi USING (ship_id) 
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items 
JOIN po_header ph ON pa.hdr_id = ph.hdr_id 
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' 
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' 
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' 
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' 
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' 
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' 
WHERE ps.eta IN ('2010-03-22','2010-03-21','2010-03-20',...)
GROUP BY ps.ship_id
LIMIT 100;

Edit: Added USE INDEX() hint as Salman A suggested. Seems like mysql see's the possible index, but choses not to use it... worth testing.

PHP_Jedi 2010-04-12 16:18:43

If I add an IN like this then it responds very quickly. However if I tighten the range condition it responds even quicker. For example ps.eta IN ('2010-03-19', '2010-03-20', '2010-03-21') responds in 0.14s whereas ps.eta >= '2010-03-19' and ps.eta <= '2010-03-21' responds in 0.06s. Unfortunately I need a bigger date range.

Damo 2010-04-12 21:23:37

Answer 12

+1 A:

I think it may be the manual sort (filesort) for the GROUP BY clause that is causing the noticeable deficit.

Try using the SQL_BIG_RESULT hint to see if MySQL will improve it's method for handling the GROUP BY.

SELECT SQL_BIG_RESULT * FROM ...

Marcus Adams 2010-04-12 17:27:06

This is very interesting. Previously when I look at the state of the connection it was always "Copying to tmp table" now with this hint it is "Sending Data". No increase in performance though. Just stays at that state for ages.

Damo 2010-04-12 21:15:59

@Damo - Sending data - doesn't that mean that the query is done? How much data would the result approx. consist of. 100KB, 1MB more ? What net connection do you use, or is it localhost? Just thought since you selects everything (*) from alot of tables. E.x export a slow result to .TXT or .CSV and check filesize.

PHP_Jedi 2010-04-13 00:55:28

It's localhost and no that much data (limit 100). No reason why it should be sending the data for so long.

Damo 2010-04-14 08:34:56

Answer 13

A:

It sure looks like you're modeling a state machine for po_shipment w/ event records in event_table for each state transition.

What is the business logic you're trying to express with this query?

You should be able to add a state field to your po_shipment to accomplish this, which summarizes and denormalizes the series of events in the event_table.

Building the logic to verify all 6 events have taken place for a given record shouldn't be in the database, it should be in your model and persisted in the database (classic state machine pattern for a model).

Then it's just select from po_shipment where state = 'MAS5' and eta > date;

Winfield 2010-04-13 02:22:59

Certainly not a state machine. It's just a Shipment with some configurable Events which are done in sequence. In the UI you see the Shipment data and then a history of the Events.

Damo 2010-04-14 08:38:04

If you can't change the structure of the query completely, I bet you can boil this down to a single join on the event_table. Join once with the condition:... event_type IN ('MAS0', 'MAS1', ... 'MAS5')

Winfield 2010-04-14 12:30:22

Answer 14

A:

Out of curiosity - does the original data in B have many NULLs?

2010-04-13 08:25:33

Both databases have lots of NULLs. Especially in the joined EVENT_TABLEs.

Damo 2010-04-14 08:35:40

ansaurus

tags:

views:

answers:

MySQL Query performance - huge difference in time

related questions