MySQL Forums
Forum List  »  Optimizer & Parser

Re: slow query sorting on group maximum
Posted by: Rick James
Date: November 07, 2010 01:05PM

If two tables are in a 1:1 relationship, why have them as separate tables? Unnecessary JOINs sometimes cost a lot (sometimes, they are perfectly fine).

There would probably be a small gain via (on `data`):
INDEX(expiry, INTID)
(This assumes you got rid of "OR expiry IS NULL".)

Why do you join to `event` at all? No fields of it are used. If you are checking for the existence of an event row before considering the patient, then ponder replacing the JOIN with an additional WHERE clause:
AND EXISTS ( SELECT * FROM event WHERE ... )
OUCH! I'm blind -- this seems worse than useless; it is a cross-join!:
join event on identity.IDENTID=intervention.PATIENTID

1.2G is reasonable for a 2G machine; be sure you are never swapping.

The NULLs, DISTINCT, etc, were to clean up the data/schema enough to then try to optimize it. Could you show me the schema, etc, after those changes:
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

As for the distinction between a 4-byte artificial PK and a 16-byte UNIQUE key being used as a PK... Yes, there are tradeoffs. At this point, I don't have enough details to make a string argument either way. (NULLness, length, number of other indexes, use in WHERE, use in GROUP/ORDER BY, etc, of the UNIQUE key are factors that weigh into this discussion.)

Options: ReplyQuote


Subject
Views
Written By
Posted
1359
November 06, 2010 10:56AM
Re: slow query sorting on group maximum
1369
November 07, 2010 01:05PM
1905
November 19, 2010 07:05PM
1294
November 19, 2010 07:20PM
1293
November 19, 2010 07:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.