MySQL Query optimization in PHP

Question

I have three tables, all of them can have possibly millions of rows. I have an actions table and a reactions table, that holds reactions related to actions. Then there is a emotes table linked to reactions. What I would like to do with this particular query, is finding the most clicked emote for a certain action. The difficulty for me is that the query includes three tables instead of only two.

Table actions (postings):

PKY id
...

Table reactions (comments, emotes etc.):

PKY id
INT action_id (related to actions table)
...

Table emotes:

PKY id
INT react_id (related to reactions table)
INT emote_id (related to a hardcoded list of available emotes)
...

The SQL query I came up with basically seems to work, but it takes 12 seconds if the tables contain millions rows. The SQL query looks like this:

select emote_id, count(*) as cnt from emotes
where react_id in (
   select id from reactions where action_id=2942715
)
group by emote_id order by cnt desc limit 1

MySQL explain says the following:

id  select_type         table       type            possible_keys       key         key_len     ref     rows    Extra
1   PRIMARY             emotes      index           NULL                react_id_2  21          NULL    4358594 Using where; Using index; Using temporary; Using f...
2   DEPENDENT SUBQUERY  reactions   unique_subquery PRIMARY,action_id   PRIMARY     8           func    1       Using where

...I am grateful for any tips for improving on the query. Note that I will NOT call this query every time a list of actions is being built, but only when emotes are being added. Therefore it's no problem if the query takes maybe 0.5 seconds to finish. But 12 is too long!

silly · Accepted Answer · 2012-12-12 12:17:26Z

1

what about this

SELECT
    emote_id,
    count(*) as cnt
FROM emotes a
INNER JOIN reactions r
    ON r.id = a.react_id
WHERE action_id = 2942715
GROUP BY emote_id
ORDER BY cnt DESC
LIMIT 1

answered Dec 12, 2012 at 12:17

silly

7,8972 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mch Over a year ago

Three rows touched, 0.0002 seconds. This helps me a lot, thanks!

mch Over a year ago

Or in native tongue: Merci ;)

Collectives™ on Stack Overflow

MySQL Query optimization in PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related