Hi @arypneta,
as you already pointed out, Embed Privacy is just using a default function of the core here. I already wanted to get rid of it, because it can produce other issues, but didn’t check for alternatives, yet.
However, it still should not produce slow queries. Do you have a large amount of authors or taxonomies in your blog?
If your host is running MySQL >= 8.0, you can prepend a EXPLAIN ANALYZE and run run the query. Then, MySQL will tell you what it actually does and where it take so long. For MariaDB, you can at least prepend EXPLAIN.
Best regards,
Matthias
We have about 180 users, but we use Co-Authors Plus which adds an additional author taxonomy (about 200 separate entries).
However, we have like 12,700 tags, so I could see that being part of the issue even though it’s not querying those directly. From the EXPLAIN ANALYZE query, it does seem like the issue is something with the taxonomy (if I’m reading it correctly, which it’s entirely possible I’m not). This particular author only has 95 posts and wp_term_taxonomy is indexed on the taxonomy field (and it’s filtering by taxonomy=”author”), so I wouldn’t think that the tags would necessarily affect this query, but maybe they are. It looks like it’s finding a ton of rows and I’m really not sure why….
-> Limit: 5 row(s) (no early end due to SQL_CALC_FOUND_ROWS) (actual time=554..554 rows=5 loops=1)
-> Sort: wp_posts.post_date DESC (actual time=554..554 rows=95 loops=1)
-> Filter: (max(if((wp_term_taxonomy.taxonomy = 'author'),if((wp_term_taxonomy.term_id = 1443),2,1),0)) <> 1) (actual time=123..554 rows=95 loops=1)
-> Stream results (cost=98692 rows=23086) (actual time=123..554 rows=95 loops=1)
-> Group aggregate: max(if((wp_term_taxonomy.taxonomy = 'author'),if((wp_term_taxonomy.term_id = 1443),2,1),0)) (cost=98692 rows=23086) (actual time=123..553 rows=95 loops=1)
-> Filter: ((wp_posts.post_author = 38) or ((wp_term_taxonomy.term_id = 1443) and (wp_term_taxonomy.taxonomy = 'author'))) (cost=83190 rows=155022) (actual time=123..553 rows=1051 loops=1)
-> Nested loop left join (cost=83190 rows=155022) (actual time=0.0749..531 rows=184775 loops=1)
-> Nested loop left join (cost=28932 rows=155022) (actual time=0.0639..128 rows=184775 loops=1)
-> Filter: ((wp_posts.post_type = 'post') and ((wp_posts.post_status = 'publish') or (wp_posts.post_status = 'acf-disabled'))) (cost=10400 rows=11563) (actual time=0.0513..29.6 rows=14549 loops=1)
-> Index scan on wp_posts using PRIMARY (cost=10400 rows=23124) (actual time=0.0496..21.7 rows=29511 loops=1)
-> Covering index lookup on tr1 using PRIMARY (object_id=wp_posts.ID) (cost=0.262 rows=13.4) (actual time=0.0035..0.00596 rows=12.7 loops=14549)
-> Single-row index lookup on wp_term_taxonomy using PRIMARY (term_taxonomy_id=tr1.term_taxonomy_id) (cost=0.25 rows=1) (actual time=0.00194..0.00196 rows=1 loops=184775)
It seems that the problem is related to the amount of data, since the query needs to process all the data in the table. You could test adding an index with taxonomy and term_id, which should improve it a lot.
Additionally, you could file a ticket in the Core track, since this is clearly something that could be improved.
Aside of that, I will check on how to remove the need of url_to_postid completely. I already have an idea and will check tomorrow if it works.
I can’t easily send a screenshot, but as far as I can tell there already are indexes on those fields:
Keyname: PRIMARY on column: term_taxonomy_id
Keyname: term_id_taxonomy on columns: term_id and taxonomy
Keyname: taxonomy on column taxonomy
But the Trac ticket is a good idea. I might search there too if there’s already one open.
That would be great if you have an idea to get around it for this plugin though. Thanks so much for your help!
You can checkout my change here. I’ll also probably ship it today within version 1.11.1:
https://github.com/epiphyt/embed-privacy/commit/9129c3e9f9cacff0b67beb836e8b5c71e86324fb
That looks great, thank you so much!
