Duplicate queries in master

[bobdenotter]

I was looking into which queries get executed in the background, and I've found that current master does quite a few duplicate queries. See a sorted dump from a generic front page:

SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"1":"1"}] - 0.21msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"1":"1"}] - 0.22msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"10":"10","4":"4","8":"8","9":"9","15":"15","14":"14"}] - 0.28msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"10":"10","4":"4","8":"8","9":"9","15":"15"}] - 0.25msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"2":"2"}] - 0.21msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"2":"2"}] - 0.22msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"3":"3"}] - 0.21msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"3":"3"}] - 0.21msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"4":"4"}] - 0.22msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["entries",{"4":"4"}] - 0.24msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["pages",{"43":"43","42":"42","39":"39","49":"49","52":"52"}] - 0.24msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["pages",{"45":"45"}] - 0.34msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["posts",{"12":"12","10":"10","11":"11","13":"13","8":"8"}] - 0.25msec 
SELECT * FROM bolt_relations WHERE from_contenttype=? AND from_id IN (?) ORDER BY id - ["showcases",{"19":"19","10":"10","14":"14","3":"3","18":"18"}] - 0.30msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"1":"1"}] - 0.18msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"1":"1"}] - 0.18msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"10":"10","4":"4","8":"8","9":"9","15":"15","14":"14"}] - 0.23msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"10":"10","4":"4","8":"8","9":"9","15":"15"}] - 0.22msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"2":"2"}] - 0.18msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"2":"2"}] - 0.18msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"3":"3"}] - 0.19msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"3":"3"}] - 0.19msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"4":"4"}] - 0.19msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["entries",{"4":"4"}] - 0.27msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["pages",{"43":"43","42":"42","39":"39","49":"49","52":"52"}] - 0.22msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["pages",{"45":"45"}] - 0.21msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["posts",{"12":"12","10":"10","11":"11","13":"13","8":"8"}] - 0.21msec 
SELECT * FROM bolt_relations WHERE to_contenttype=? AND to_id IN (?) ORDER BY id - ["showcases",{"19":"19","10":"10","14":"14","3":"3","18":"18"}] - 0.22msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"1":"1"},"entries",["tags","chapters","categories"]] - 0.37msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"1":"1"},"entries",["tags","chapters","categories"]] - 0.39msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"10":"10","4":"4","8":"8","9":"9","15":"15","14":"14"},"entries",["tags","chapters","categories"]] - 0.60msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"10":"10","4":"4","8":"8","9":"9","15":"15"},"entries",["tags","chapters","categories"]] - 0.48msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"12":"12","10":"10","11":"11","13":"13","8":"8"},"posts",["tags","chapters","categories"]] - 0.48msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"19":"19","10":"10","14":"14","3":"3","18":"18"},"showcases",["tags","chapters","categories"]] - 0.52msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"2":"2"},"entries",["tags","chapters","categories"]] - 0.38msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"2":"2"},"entries",["tags","chapters","categories"]] - 0.48msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"3":"3"},"entries",["tags","chapters","categories"]] - 0.36msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"3":"3"},"entries",["tags","chapters","categories"]] - 0.37msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"4":"4"},"entries",["tags","chapters","categories"]] - 0.35msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"4":"4"},"entries",["tags","chapters","categories"]] - 0.38msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"43":"43","42":"42","39":"39","49":"49","52":"52"},"pages",["tags","chapters","categories"]] - 0.37msec 
SELECT * FROM bolt_taxonomy WHERE content_id IN (?) AND contenttype=? AND taxonomytype IN (?) - [{"45":"45"},"pages",["tags","chapters","categories"]] - 0.45msec 
SELECT * FROM bolt_users users - [] - 0.24msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.16msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.17msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.17msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.17msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.17msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.17msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.18msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.19msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.19msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.20msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.20msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.20msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.20msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.20msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.21msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.21msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.22msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.26msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"1"} - 0.29msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"2"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"2"} - 0.15msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"2"} - 0.16msec 
SELECT * FROM bolt_users users WHERE id = :userId - {"userId":"2"} - 0.20msec 
SELECT bolt_entries.* FROM bolt_entries ORDER BY `datepublish` DESC LIMIT 5 - [] - 0.17msec 
SELECT bolt_entries.* FROM bolt_entries ORDER BY `datepublish` DESC LIMIT 6 OFFSET 0 - [] - 0.24msec 
SELECT bolt_entries.* FROM bolt_entries WHERE (`bolt_entries`.`id` = '1') ORDER BY `datepublish` DESC LIMIT 1 - [] - 0.23msec 
SELECT bolt_entries.* FROM bolt_entries WHERE (`bolt_entries`.`id` = '1') ORDER BY `datepublish` DESC LIMIT 1 - [] - 0.31msec 
SELECT bolt_entries.* FROM bolt_entries WHERE (`bolt_entries`.`id` = '2') ORDER BY `datepublish` DESC LIMIT 1 - [] - 0.17msec 
SELECT bolt_entries.* FROM bolt_entries WHERE (`bolt_entries`.`id` = '2') ORDER BY `datepublish` DESC LIMIT 1 - [] - 0.29msec 
SELECT bolt_entries.* FROM bolt_entries WHERE (`bolt_entries`.`id` = '3') ORDER BY `datepublish` DESC LIMIT 1 - [] - 0.14msec 
SELECT bolt_entries.* FROM bolt_entries WHERE (`bolt_entries`.`id` = '3') ORDER BY `datepublish` DESC LIMIT 1 - [] - 0.19msec 
SELECT bolt_entries.* FROM bolt_entries WHERE (`bolt_entries`.`id` = '4') ORDER BY `datepublish` DESC LIMIT 1 - [] - 0.13msec 
SELECT bolt_entries.* FROM bolt_entries WHERE (`bolt_entries`.`id` = '4') ORDER BY `datepublish` DESC LIMIT 1 - [] - 0.24msec 
SELECT bolt_pages.* FROM bolt_pages ORDER BY `datepublish` DESC LIMIT 5 - [] - 0.21msec 
SELECT bolt_pages.* FROM bolt_pages WHERE (`bolt_pages`.`id` = '1') ORDER BY datepublish DESC LIMIT 1 - [] - 0.22msec 
SELECT bolt_pages.* FROM bolt_pages WHERE (`bolt_pages`.`id` = '3') ORDER BY datepublish DESC LIMIT 1 - [] - 0.13msec 
SELECT bolt_pages.* FROM bolt_pages WHERE (`bolt_pages`.`id` = '3') ORDER BY datepublish DESC LIMIT 1 - [] - 0.16msec 
SELECT bolt_pages.* FROM bolt_pages WHERE (`bolt_pages`.`id` = '45') ORDER BY datepublish DESC LIMIT 1 - [] - 0.23msec 
SELECT bolt_pages.* FROM bolt_pages WHERE (`bolt_pages`.`slug` = 'about') ORDER BY datepublish DESC LIMIT 1 - [] - 0.25msec 
SELECT bolt_posts.* FROM bolt_posts ORDER BY `datepublish` DESC LIMIT 5 - [] - 0.17msec 
SELECT bolt_showcases.* FROM bolt_showcases ORDER BY `datepublish` DESC LIMIT 5 - [] - 0.21msec 
SELECT COUNT(*) as count FROM bolt_entries - [] - 0.18msec 
SELECT COUNT(*) as count FROM bolt_entries - [] - 0.25msec 
SELECT COUNT(*) as count FROM bolt_pages - [] - 0.11msec 
SELECT COUNT(*) as count FROM bolt_posts - [] - 0.13msec 
SELECT COUNT(*) as count FROM bolt_showcases - [] - 0.19msec 
SELECT id FROM bolt_entries WHERE (status = :oldstatus) AND (datedepublish <= :currenttime) AND (datedepublish > :zeroday) AND (datechanged < datedepublish) - {"datechanged":"2015-11-15 13:57:43","oldstatus":"published","newstatus":"held","zeroday":"1900-01-01 00:00:01","currenttime":{"date":"2015-11-15 13:57:43.000000","timezone_type":3,"timezone":"Europe\/Amsterdam"}} - 0.30msec 
SELECT id FROM bolt_entries WHERE (status = :oldstatus) AND (datepublish < :currenttime) - {"datechanged":"2015-11-15 13:57:43","oldstatus":"timed","newstatus":"published","currenttime":{"date":"2015-11-15 13:57:43.000000","timezone_type":3,"timezone":"Europe\/Amsterdam"}} - 0.35msec 
SELECT id FROM bolt_pages WHERE (status = :oldstatus) AND (datedepublish <= :currenttime) AND (datedepublish > :zeroday) AND (datechanged < datedepublish) - {"datechanged":"2015-11-15 13:57:43","oldstatus":"published","newstatus":"held","zeroday":"1900-01-01 00:00:01","currenttime":{"date":"2015-11-15 13:57:43.000000","timezone_type":3,"timezone":"Europe\/Amsterdam"}} - 0.23msec 
SELECT id FROM bolt_pages WHERE (status = :oldstatus) AND (datepublish < :currenttime) - {"datechanged":"2015-11-15 13:57:43","oldstatus":"timed","newstatus":"published","currenttime":{"date":"2015-11-15 13:57:43.000000","timezone_type":3,"timezone":"Europe\/Amsterdam"}} - 0.22msec 
SELECT id FROM bolt_posts WHERE (status = :oldstatus) AND (datedepublish <= :currenttime) AND (datedepublish > :zeroday) AND (datechanged < datedepublish) - {"datechanged":"2015-11-15 13:57:43","oldstatus":"published","newstatus":"held","zeroday":"1900-01-01 00:00:01","currenttime":{"date":"2015-11-15 13:57:43.000000","timezone_type":3,"timezone":"Europe\/Amsterdam"}} - 0.25msec 
SELECT id FROM bolt_posts WHERE (status = :oldstatus) AND (datepublish < :currenttime) - {"datechanged":"2015-11-15 13:57:43","oldstatus":"timed","newstatus":"published","currenttime":{"date":"2015-11-15 13:57:43.000000","timezone_type":3,"timezone":"Europe\/Amsterdam"}} - 0.32msec 
SELECT id FROM bolt_showcases WHERE (status = :oldstatus) AND (datedepublish <= :currenttime) AND (datedepublish > :zeroday) AND (datechanged < datedepublish) - {"datechanged":"2015-11-15 13:57:43","oldstatus":"published","newstatus":"held","zeroday":"1900-01-01 00:00:01","currenttime":{"date":"2015-11-15 13:57:43.000000","timezone_type":3,"timezone":"Europe\/Amsterdam"}} - 0.23msec 
SELECT id FROM bolt_showcases WHERE (status = :oldstatus) AND (datepublish < :currenttime) - {"datechanged":"2015-11-15 13:57:43","oldstatus":"timed","newstatus":"published","currenttime":{"date":"2015-11-15 13:57:43.000000","timezone_type":3,"timezone":"Europe\/Amsterdam"}} - 0.24msec 

Should be possible to shave off some of those duplicates. Especially in the SELECT * FROM bolt_users ones.

[rarila]

Ouch

[rossriley]

I know about the relation / taxonomy ones... Just haven't got round to optimizing yet, I'm going to make the collection lazy so they should all get loaded at once in one query.

[bobdenotter]

@rossriley Cool! :-)

in the meantime, the "user" ones are fixed in #4455.

[SvanteRichter]

Updates? (asking since I'm going for "pain in the ass" today)

[rossriley]

I'm starting on this now. PR coming soon.

[rossriley]

Mitigated in #4832