Re: Optimize subquery, replace IN() with JOIN
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.
Subject
Views
Written By
Posted
2401
November 15, 2016 02:37PM
972
November 15, 2016 03:34PM
1112
November 15, 2016 04:25PM
953
November 17, 2016 08:11PM
930
November 22, 2016 02:44PM
941
November 16, 2016 04:03AM
966
November 16, 2016 12:51PM
1076
November 16, 2016 03:22PM
Re: Optimize subquery, replace IN() with JOIN
926
November 16, 2016 04:44PM
721
December 27, 2016 05:32AM
829
November 16, 2016 01:50AM
880
November 16, 2016 04:01AM
1037
November 16, 2016 06:23AM