Select / Order By performance issues
Development and Programming
Got a client where the CP Edit page with entry listings can take up to a minute (!) to load. Got New Relic running and that points to 2 queries: one that returns the numrows for the current entry listing, and the other that actually fetches the entries (25 by default).
By the looks of it, it’s the order by part that’s making it very slow. Tried extra indexes, switching between MyISAM and InnoDB… No dice. Copying over the query to Sequel Pro and just running that also is slow.
There are over 50k of entries in the DB, so not a huge amount.
Same is true for the front-end, but there it really helps to set the start_on="-2 weeks" parameter to the channel:entries tag, to limit the entries to the latest, rather than just relying on orderby="date" sort="desc" limit="25".
The site is on EE5.1.2. Any pointers on how to improve the performance would be appreciated.
FWIW, looking at the EXPLAIN data from the query, it looks like MySQL is creating a tmp table to get the ordered results, and that takes a lot of time. Is this a common bottleneck?
When you run it in manually, is it doing where site_id = 1 and is it an MSM site? If it is and you drop that from the query, any difference?
Yes, it is a MSM site. However, 2 of the 3 sites are no longer active, even though the content is still used on site 1. Take this query as example:
SELECT COUNT(*) AS `numrows`
FROM (`exp_channel_titles` as ChannelEntry_channel_titles, `exp_channel_data` as ChannelEntry_channel_data)
LEFT JOIN `exp_channels` AS Channel_channels ON `Channel_channels`.`channel_id` = `ChannelEntry_channel_titles`.`channel_id`
LEFT JOIN `exp_members` AS Author_members ON `Author_members`.`member_id` = `ChannelEntry_channel_titles`.`author_id`
LEFT JOIN `exp_member_data` as Author_member_data ON `Author_member_data`.`member_id` = `Author_members`.`member_id`
LEFT JOIN `exp_channel_entries_autosave` AS Autosaves_channel_entries_autosave ON `Autosaves_channel_entries_autosave`.`original_entry_id` = `ChannelEntry_channel_titles`.`entry_id`
WHERE ChannelEntry_channel_data.entry_id = ChannelEntry_channel_titles.entry_id
AND (
`ChannelEntry_channel_titles`.`site_id` = 1
)
LIMIT 18446744073709551615
This takes 1.6s to load in my local environment (and a lot longer on the live environment when there’s more traffic). Taking out the site_id check speeds it up to 0.7s. (and results in ~170,000 records)
Other queries show significant improvements when I comment out the site_id where clause, too.
have you looked at server load during the query? is it CPU bound? is the db engine configured to make good use if the available RAM?
@meatpaste There is server load, yes. It’s a site with quite some traffic, but it’s a chicken/egg situation. There’s a high server load because, apparently, MySQL is creating a tmp table to sort a large-ish table. The db server is dedicated (but not under my control) and should be optimized for this site’s use.
@Robin any more pointers or suggestions to improve performance?
What version of MySQL @Low?
@derek MySQL 5.5.62-cll.
FWIW, changing the engine to InnoDB has made other improvements (avoiding table locks), but the performance issue for these kind select statements persists.
Odd; I’ve not seen it on v5.5 but it sure does sound similar to this MySQL optimizer bug. Some folks have had success disabling block_nested_loop, but others have had that cause new, separate problems. All users we know who have encountered this and switched to MariaDB the problem has gone away.