Skip to main content
Topic: EXPLAIN (Read 1752 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

EXPLAIN

See the two attachments:
screen_150.png is a query that takes 20 seconds to execute,
screen_149.png takes 0.5 seconds.
The table I removed from the query in screen_149.png has about 5M rows.

Am I blind or EXPLAIN doesn't give me any hint about that behaviour?

Re: EXPLAIN

Reply #1

QuoteAm I blind or EXPLAIN doesn't give me any hint about that behaviour?
EXPLAIN isn't very communicative, the only hint you'll get is:
USING TEMPORARY TABLE
USING FILESORT

both not good for performance.

Re: EXPLAIN

Reply #2

Just a guess.

That table has several possible keys, I've seen the optimizer not always pick the best one ... you could use force index (some index) and see using another one other than what its choosing is better.  Also the key_len, what is that 8 bytes, thats a long key, so lots of large values to check against.

Re: EXPLAIN

Reply #3

Luckily there are drafts! :D

I forgot the answer open somewhere and probably the other day when I had to kill X to get my KDE back I probably lost it...

The full query is this:
Code: [Select]
SELECT t.*, t1.real_name, t1.gender, t1.avatar, t2.id_folder, t2.file_hash,
 IFNULL(t2.id_attach, '') AS attach, IFNULL(t2.filename, '') AS filename,
/* IFNULL(t3.is_read, '') AS is_read,*/
 IFNULL(t4.online_color, '') AS online_color,
 IFNULL(t5.id_member, 0) AS logged
 FROM smf_pmxblog_manager AS t
 LEFT JOIN smf_members AS t1 ON (t1.id_member = t.owner)
 LEFT JOIN smf_pmxblog_content AS c ON (c.owner = t.owner)
 LEFT JOIN smf_attachments AS t2 ON (t2.id_member = t.owner)
/* LEFT JOIN smf_pmxblog_cont_log AS t3 ON (t3.owner = t.owner AND t3.userID = 157421)*/
 LEFT JOIN smf_membergroups AS t4 ON(t1.id_group = t4.id_group OR t1.id_post_group = t4.id_group)
 LEFT JOIN smf_log_online AS t5 ON(t.owner = t5.id_member)
 WHERE t.owner = 157421
 OR (blogenabled != 0
 AND bloglocked = 0)
 GROUP BY t.owner
 HAVING COUNT(c.ID) > 0
 ORDER BY t1.real_name ASC

The commented out pieces are the difference between the two screens.

The structure of the smf_pmxblog_cont_log table is:
owner int(10) UNSIGNED
userID int(10) UNSIGNED
is_read tinytext

There are three indexes, one on owner, one on userID and one (that I added for test, and that is used in the query of screen_150) on the pair owner-userID.
I tried to force other indexes but no joy...always 20 seconds.

Well, I split the query in two now so that all is nice, I'll try to investigate sooner or later the reason of this behaviour.

Re: EXPLAIN

Reply #4

Is that AND really in that join or was that just a test? ... could that be moved to the WHERE?  Sounds like splitting it up worked so thats good news.

Re: EXPLAIN

Reply #5

Good point... I'll give it a try, I'm curious now. ;D

Re: EXPLAIN

Reply #6

Been a while since I worked with SQL so excuse me if I'm totally off.

Could you post the schema?

I changed the aliases to make it easier for me to read:
Code: [Select]
SELECT bm.*, mem.real_name, mem.gender, mem.avatar, a.id_folder, a.file_hash
 , IFNULL(a.id_attach, '') AS attach, IFNULL(a.filename, '') AS filename
 , IFNULL(clog.is_read, '') AS is_read
 , IFNULL(mg.online_color, '') AS online_color
 , IFNULL(lo.id_member, 0) AS logged
 
 FROM smf_pmxblog_manager AS bm
 LEFT JOIN smf_members AS mem ON (mem.id_member = bm.owner)
 LEFT JOIN smf_pmxblog_content AS c ON (c.owner = bm.owner)
 LEFT JOIN smf_attachments AS a ON (a.id_member = bm.owner)
 LEFT JOIN smf_pmxblog_cont_log AS clog ON (clog.owner = bm.owner AND clog.userID = 157421)
 LEFT JOIN smf_membergroups AS mg ON(mem.id_group = mg.id_group OR mem.id_post_group = mg.id_group)
 LEFT JOIN smf_log_online AS lo ON(bm.owner = lo.id_member)
 
 WHERE bm.owner = 157421
 OR (blogenabled != 0 AND bloglocked = 0)
 GROUP BY bm.owner
 HAVING COUNT(c.ID) > 0
 ORDER BY mem.real_name ASC

Remove the other indexes on the content_log. It should only have one: UNIQUE(owner, userID). Make the is_read a varchar(255). That should solve some of those issues.

Is there an index on t.owner? Is owner the PK? Not sure why it isn't using the PK but the optimizer checks to see the size of the table and what rows you want. If it thinks it is faster to just read the entire file, it will do that. Filesort isn't always a bad thing, just usually. Pretty sure the filesort comes from t.blogenabled and t.bloglocked (pretty sure those are t fields) not having an index. You would want UNIQUE(owner, blogenabled, bloglocked). If I were you though, if you aren't going to use IN() on t.owner, I would use "HAVING blogenabled != 0 AND bloglocked = 0" or get the row and then do the check on blogenabled and bloglocked in the application.

I don't think there is a need for the GROUP BY, right?

If you want to do HAVING COUNT(c.ID) > 0, why not just make that an INNER JOIN? Then it is requiring that field in order to get the rest of the fields.

Since t.owner is a constant and you reference t.owner all over, why not just use that constant? Might save the optimizer some time trying to figure that out. I am kind of surprised the EXPLAIN doesn't show that it replaces t.owner to the constant.

If the member is not in the members table, would that break everything in the application? If so, I think an INNER JOIN is what you should use there.

Not really having to do with the issue, but when you do your checks if a field is empty in PHP, you probably use empty(), right? If so, just leave off the IFNULL() stuff. If it is null, it will be null in PHP and then empty() will see it as being null. I would say use IFNULL() when you want to change null to another value that wouldn't equate to null or you're not using empty(). Considering that Elkarte/SMF never allows for nulls in the fields, it will be an empty string, 0, or there will be no field anyway.

If this is for Elkarte, I would use the get_members() (or whatever the function is called) function and split that off.

After the changes, this is my udpated query:
Code: [Select]
SELECT bm.*, clog.is_read AS is_read
 , mem.real_name, mem.gender, mem.avatar
 , a.id_folder, a.file_hash, a.id_attach, a.filename
 , lo.id_member AS logged
 , mg.online_color
FROM smf_pmxblog_manager AS bm
 INNER JOIN smf_pmxblog_content AS c ON (c.owner = 157421)
 LEFT JOIN smf_pmxblog_cont_log AS clog ON (clog.owner = 157421 AND clog.userID = 157421)
 LEFT JOIN smf_members AS mem ON (mem.id_member = 157421)
 LEFT JOIN smf_attachments AS a ON (a.id_member = 157421)
 LEFT JOIN smf_log_online AS lo ON(lo.id_member = 157421)
 LEFT JOIN smf_membergroups AS mg ON(mem.id_group = mg.id_group OR mem.id_post_group = mg.id_group)
WHERE bm.owner = 157421
ORDER BY mem.real_name ASC