MySQL Forums
Forum List  »  Optimizer & Parser

Re: same query sometimes very slow (punbb forum)
Posted by: Rick James
Date: January 07, 2011 11:03PM

(for readability...)
SELECT, u.title, u.url, u.location,
        u.use_avatar, u.signature, u.email_setting,
        u.num_posts, u.registered, u.admin_note,
        u.reputation_enable,, p.poster AS username,
        p.poster_id, p.poster_ip, LOWER(p.isocc) as isocc,
        p.poster_email, p.message, p.hide_smilies,
        p.posted, p.edited, p.edited_by,
        o.user_id AS is_online,
        IsShielded(27,p.poster_id) AS shield,
      ( SELECT  count(*)
            from  reputation rp
            where  rp.post_id = as repos, 
      ( SELECT  SUM(r1.rep_plus)
            from  reputation r1
            where  r1.user_id = p.poster_id) as count_rep_plus,
      ( SELECT  SUM(r2.rep_minus)
            from  reputation r2
            where  r2.user_id = p.poster_id) as count_rep_minus
    FROM  posts AS p
    INNER JOIN  users  AS u ON
    INNER JOIN  groups AS g ON g.g_id=u.group_id
    LEFT JOIN   online AS o ON (
                           AND  o.user_id!=1
                           AND  o.idle=0)
    WHERE  p.topic_id=8993
    LIMIT  25050,50;

LIMIT 25050,50;
has to skip over 25050 rows, then deliver 50. It won't be efficient.

Are there 25K comments on topic #8993 ? Time to stop that topic and start fresh.

Since you are JOINing two (or more tables) and filtering (WHERE...) on both of them, MySQL has to gather all the possible rows, then sort them (ORDER BY), skip over 25050 (OFFSET), and finally deliver 50 (LIMIT).

Is there any way to avoid filtering on o.user_id!=1 AND o.idle=0 ? (Hmmmm... Since it is a LEFT JOIN, I could be wrong.)

Please do SHOW CREATE TABLE for the other tables.

p might benefit from
INDEX(topic_id, user_id)

"Prefix" indexes
KEY `users_username_idx` (`username`(8)),
are virtually useless. Either DROP the index, or remove the (8).

ROW_FORMAT=FIXED -- Why? It is rarely advantageous, and usually wastes a lot of disk and cache. I think I know what your answer will be, in which case I will point out that the TEXT column invalidates that reason.

SHOW TABLE STATUS -- I need to see the sizes of all the tables.

SHOW VARIABLES LIKE 'key_buffer_size';
How much RAM do you have?

What is IsShielded() ?

The 3 subqueries ((SELECT...)) are probably being executed 25050+50 times. This we might be able to avoid. More about that after we get past some of these other issues.

Options: ReplyQuote

Written By
Re: same query sometimes very slow (punbb forum)
January 07, 2011 11:03PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.