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
2202
November 15, 2016 02:37PM
886
November 15, 2016 03:34PM
984
November 15, 2016 04:25PM
874
November 17, 2016 08:11PM
801
November 22, 2016 02:44PM
853
November 16, 2016 04:03AM
857
November 16, 2016 12:51PM
991
November 16, 2016 03:22PM
Re: Optimize subquery, replace IN() with JOIN
833
November 16, 2016 04:44PM
596
December 27, 2016 05:32AM
741
November 16, 2016 01:50AM
782
November 16, 2016 04:01AM
938
November 16, 2016 06:23AM