MySQL Forums
Forum List  »  MyISAM

Re: Help to optimize query
Posted by: Rick James
Date: December 02, 2012 09:23AM

The first SELECT found about 32 million rows to scan. There seems to be no way to optimize that part, since the WHERE clause touches only timestamp.

SHOW CREATE TABLE would be helpful -- why is timestamp 8 bytes? Is fk_id_ad_format indexed? Would it matter? Please provide SHOW TABLE STATUS also.

UNION defaults to UNION DISTINCT, which usually needs a de-dupping pass. Would UNION ALL work for you?

> FROM `spd_log_view` view
Is a VIEW involved? They do not optimize well. See if the equivalent SELECT would run faster.

5.5 defaults to InnoDB. So, you are explicitly requesting MyISAM? Why?

key_buffer_size defaults much to low for such large MyISAM tables. See this for better tuning:
http://mysql.rjweb.org/doc.php/memory

Give this a try...
SELECT ...
FROM ( SELECT ... UNION SELECT ... ) a -- Note 1
JOIN ... -- Note 2

Note 1: If possible put only `view` and `click` in the subquery with the UNION.
Note 2: Then put the "normalization lookups" outside. (They seem to be identical in your two SELECTs.)

Then carry it a step further... Note that there are 4*(32M+68K) lookups. These seem to happen before the COUNT(*) occurs. So, let's try to move them out:
SELECT ...
FROM ( SELECT ..., COUNT(*) -- Note 3
FROM ( SELECT ... UNION SELECT ... ) b -- Note 1
GROUP BY ... -- Note 3
) c
JOIN ... -- Note 2

Note 3 -- Do the COUNT(*) and GROUP BY between the UNION and the lookups.

Usually it is better to turn subqueries into JOINs. This is likely to be a case where the opposite is better. The tip off is the GROUP BY. It is best to push that into a subquery since that leads to fewer rows, sooner. The complexity comes in the UNION. It _seems_ (without fully understanding your app) that the UNION may as well be a subquery at the lowest level.

Probably the best performance boost would come from developing "summary tables" and using them for the query. This is discussed frequently in the Performance forum.

Options: ReplyQuote


Subject
Views
Written By
Posted
2772
November 27, 2012 12:27PM
Re: Help to optimize query
1549
December 02, 2012 09:23AM


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.