MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize subquery, replace IN() with JOIN
Posted by: Peter Brawley
Date: November 16, 2016 04:44PM


Explain says the old query will materialise the subqueries, and will require the engine to examine 11 billion rows.

I too am curious about how much this changes with optimizer_switch="materialization=off".

It says the join query will require reading just 799 rows, nearly 14 million times fewer rows. We might've set a record.

Further optimisations are possible. MySQL can use one index per Explain line, but all these table indexes are on single columns. Queries like this one need multi-column covering indexes. Rule of thumb: Where columns, them Join columns. Experiment.

About innodb_buffer_pool_size, 5120M (5G) looks low for a system with >100G. The usual rule of thumb is two-thirds to three-quarters of available RAM.

Worse, swap use is deadly for MySQL performance, should always be zero.

But are you running MySQL in a cloud? I'm not at all familiar with Cloudlinux or with MySQL Governor. If so, the first order of business is to maximise innodb_buffer_pool_size such that there is never any swapping.

Options: ReplyQuote

Written By
Re: Optimize subquery, replace IN() with JOIN
November 16, 2016 04:44PM

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.