index-wp-mysql-for-speed
index-wp-mysql-for-speed copied to clipboard
OllieJones
slist like ilist
It's a query fingerprint (percona calls them query hashes) thing....
WP does queries like
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (627329,627330,627331,627332,627333,627334,627335,627336,627337,627338,627339,627340,627341,627342,627343,627344,627345,627346,627347,627348,627349,627350,627351,627352,627353,627354,627355,627356,627357,627358,627359,627360,627361,627362,627363,627364,627365,627366,627367,627368,627369,627370,627371,627372,627373,627374,627375,627376,627377,627378,627379,627380,627381,627382,627383,627384,627385,627386,627387,627388,627389,627390,627391,627392,627393,627394,627395,627396,627397,627398,627399,627400,627401,627402,627403,627404,627405,627406,627407,627408,627409,627410,627411,627412,627413,627414,627415,627416,627417,627418,627419,627420,627421,627422,627423,627424,627425,627426,627427,627428,627429,627430,627431,627432,627433,627434,627435,627436,627437,627438,627439,627440,627441,627442,627443,627444,627445,627446,627447,627448,627449) ORDER BY meta_id ASC
The fingerprint for that is
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (?ilonglist?) ORDER BY meta_id ASC
For shorter lists it's ?ilist? instead. The longlists should start at length 21, because WP does a lot of pagination with 20 rows at a time.
And we sometimes get stuff like this, with strings rather than numbers in the queries.
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN ('627329','627330','627331','627332','627333') ORDER BY meta_id ASC
Right now the fingerprinting turns that into
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN ('?s?','?s?','?s?','?s?','?s?') ORDER BY meta_id ASC
which means the fingerprint scheme doesn't consolidate similar queries very well.
My fingerprints for those IN clauses were IN (N,N,N,N,N,N,N) or IN (S,S,S,S,S,S,S)where the number of N's was log2 of the number of IN values. WP gives you 2 list sizes; my approach is more fine-grained.In particular, IN(1) is worth distinguishing because the Optimizer turns it into =1, there by leading to more optimization possibilities. (I don't remember if I noticed that the strings were numeric and used N instead of S.) In your example, a single-item IN list would allow for eliminating the sort (for ORDER BY), given INDEX(post_id, meta_id) So, when I see IN(1), I assume that sometimes there will be 1 item; sometimes there will be multiple items. Then I devise INDEX(es) accordingly. (Most times, simply assuming =1 leads to a single, sufficiently efficient, INDEX.) Another issue... 'number' is almost always treated as simply number . The rule is: varchar-column = non-quoted-numberis slow because it will convert the column to a number before testing, making it not sargable.The other three combinations are 'fast'. 70K items in an IN list is the most I have 'seen'. (That would take 17 N's, which is quite tolerable for a digest.)
Rick Jamesemail: @.***
On Monday, December 13, 2021, 11:01:48 AM PST, Oliver Jones ***@***.***> wrote:
It's a query fingerprint (percona calls them query hashes) thing....
WP does queries like
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (627329,627330,627331,627332,627333,627334,627335,627336,627337,627338,627339,627340,627341,627342,627343,627344,627345,627346,627347,627348,627349,627350,627351,627352,627353,627354,627355,627356,627357,627358,627359,627360,627361,627362,627363,627364,627365,627366,627367,627368,627369,627370,627371,627372,627373,627374,627375,627376,627377,627378,627379,627380,627381,627382,627383,627384,627385,627386,627387,627388,627389,627390,627391,627392,627393,627394,627395,627396,627397,627398,627399,627400,627401,627402,627403,627404,627405,627406,627407,627408,627409,627410,627411,627412,627413,627414,627415,627416,627417,627418,627419,627420,627421,627422,627423,627424,627425,627426,627427,627428,627429,627430,627431,627432,627433,627434,627435,627436,627437,627438,627439,627440,627441,627442,627443,627444,627445,627446,627447,627448,627449) ORDER BY meta_id ASC
The fingerprint for that is
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (?ilonglist?) ORDER BY meta_id ASC
For shorter lists it's ?ilist? instead. The longlists should start at length 21, because WP does a lot of pagination with 20 rows at a time.
And we sometimes get stuff like this, with strings rather than numbers in the queries.
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN ('627329','627330','627331','627332','627333') ORDER BY meta_id ASC
Right now the fingerprinting turns that into
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN ('?s?','?s?','?s?','?s?','?s?') ORDER BY meta_id ASC
which means the fingerprint scheme doesn't consolidate similar queries very well.
— You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android.