April 3, 2019 at 6:00 pm
Every page asks me (at the very bottom) to "take a peek into our servers". So, I figured why not. I clicked the link ( https://monitor.red-gate.com/overviews/SQLMON-EC2-BM2/cluster/sscdbcluster.ssc.local/sql/ssc-db-n1%5C(local)#?Zoom=1554304217018%2C1554311417018&MaxTime=1554311417018&Present=true ). It took me to a "SQL Monitor Live Demo" page. Neat. It has a list of the "Top 50 queries". Neat. I scrolled through and found one that was using more logical reads than the rest (450k compared to roughly 19k for most of the others):
SELECT wp_posts.* , wp_posts.post_date
FROM wp_posts
WHERE 1=1
AND ((
(wp_posts.post_title LIKE '%turbo.asp%')
OR (wp_posts.post_excerpt LIKE '%turbo.asp%')
OR (wp_posts.post_content LIKE '%turbo.asp%')))
AND (wp_posts.post_password = '')
AND wp_posts.post_type IN ('post', 'page', 'attachment', 'ssc_article',
'ssc_blog_post', 'ssc_book', 'ssc_databaseweekly',
'ssc_editorial', 'ssc_script', 'ssc_stairway')
AND ( wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'closed'
OR wp_posts.post_status = 'acf-disabled')
ORDER BY wp_posts.post_date DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
And this beauty with a measly 3 million logical reads:
SELECT DISTINCT wp_posts.* , wp_postmeta.meta_value
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND ( wp_postmeta.meta_key = '_bbp_last_active_time' )
AND wp_posts.post_type = 'topic'
AND ( (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'closed') )
ORDER BY meta_value DESC OFFSET 265065 ROWS FETCH NEXT 15 ROWS ONLY
Now, I know that you (Red Gate) did not create WordPress and thus have no control over the schema, so we can't fault you for the horrible DB design anti-pattern of using string columns for "post_status" and "post_type". I'm not sure if you were fully aware as I just learned about this a few minutes ago when I checked a WP install I have running on Linux and saw it, but every single edit of a post has what appears to be the full text on every row. So every edit of every article and every forum post / reply just grows that "wp_posts" table. Given the amount of traffic you get in the forums, the number of rows in the "wp_posts" table is likely to grow rapidly (at least I assume Red Gate expects continued / growing traffic), and I assume that you do know what the effect will be, searching through multiple string columns (assuming "post_status" and "post_type" are commonly filtered on) over millions and millions of rows, even with indexes.
I am mentioning this because something that most folks do not think about is collation when it comes to "code" values (i.e. simple lookup values). These values either have a single case such that case is irrelevant, or are intentionally mixed case because the values are case-sensitive. In either scenario, there is never a need to be concerned about linguistic/dictionary rules for any particular culture. Hence, it is best to use a binary collation on such columns (i.e. Latin1_General_100_BIN2 ). This will improve performance in a situation where you cannot make the ideal change of converting these two columns into TINYINT lookup values (even INT would be an improvement), but changing the collation isn't really changing the schema, hence not breaking the WP install and not complicating future upgrades of it.
If you have already done this (or planned on doing it), then awesome and please ignore this post ;-).
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply