Re: Optimize subquery, replace IN() with JOIN

[Peter Brawley]

Miron,

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.