Long Sql Query optimization

Question

I have a single sql query that takes down my whole server when there are many people logged in to the site.

i spent all day trying to find out how to fix it with tutorials for indexing but i still can't understand how can i fix this query or if i can at all

This is the query:

SELECT t.id, 
       t.title, 
       t.s_secret, 
       t.content, 
       t.senton, 
       t.hidden, 
       t.reported, 
       t.root_id, 
       t.sender_id, 
       t.s_contact_name, 
       t.s_contact_email, 
       t.item_id, 
       t.status_id, 
       t.event_id, 
       l.id              AS last_id, 
       l.title           AS last_title, 
       l.s_secret        AS last_s_secret, 
       l.content         AS last_content, 
       l.senton          AS last_sentOn, 
       l.hidden          AS last_hidden, 
       l.reported        AS last_reported, 
       l.root_id         AS last_root_id, 
       l.sender_id       AS last_sender_id, 
       l.s_contact_name  AS last_s_contact_name, 
       l.s_contact_email AS last_s_contact_email, 
       l.item_id         AS last_item_id, 
       l.status_id       AS last_status_id, 
       l.event_id        AS last_event_id, 
       last.count        AS t_count 
FROM   oc_t_mmessenger_recipients r 
       JOIN oc_t_mmessenger_message l 
         ON l.id = r.message_id 
       JOIN (SELECT Max(im.id) AS max, 
                    Count(1)   AS count 
             FROM   oc_t_mmessenger_message im 
             GROUP  BY root_id) last 
         ON r.message_id = last.max 
       JOIN oc_t_mmessenger_message t 
         ON t.id = l.root_id 
       JOIN oc_t_mmessenger_message_labels ml 
         ON ( ml.fk_i_message_id = t.id 
              AND ml.fk_i_label_id = 1 
              AND ml.fk_i_user_id = 2569 ) 
WHERE  ( r.recipient_id = 2469 
          OR l.sender_id = 2469 ) 
ORDER  BY last.max DESC 
LIMIT  0, 10

If i created index through phpmyadmin for tables like recipient_id and sender_id will it help at all? Any tip will be greatly appreciated! Thanks.

Edit:

This is the indexes of current tables:

The first inner select reads the WHOLE table oc_t_mmessenger_message. Can you post which indexes do you currently have? — The Impaler, Apr 20 '18 at 20:54
Thanks, i edited the post and added the image of indexes at the bottom for tables oc_t_messenger_message and oc_t_messenger_recipients — AlexGr, Apr 20 '18 at 21:04

score 2 · Answer 1 · answered Apr 20 '18 at 21:22

You might want to look in to splitting your query in to two instead of the OR-statement. like:

(SELECT 
# everything from your query
WHERE r.recipient_id = 2469)
UNION
(SELECT
# everything from your query, again
WHERE l.sender_id = 2469)

This way you should be able to use both the recipient_id and the sender_id indexes.

But as the others are saying, the biggest problem is probably the inner select.

score 1 · Accepted Answer · answered Apr 20 '18 at 21:10

1

OK, you have several indexes I was thinking about, so you are not that far away. Now that inner select is killing the performance.

The following index should speed up the inner select:

create index ix1_message on oc_t_mmessenger_message (root_id, id);

answered Apr 20 '18 at 21:10

The Impaler

45,731
9
39
76

Thank you, i added this index but (this may be a dump question) isn't there already root_id and id index already? also i read [here](https://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.admin.nls.doc/doc/c0053263.html) that "The collation you choose can significantly impact the performance of queries in the database" I am using utf8_general_ci could i change it to something faster without loosing anything? – AlexGr Apr 21 '18 at 12:20
A combined index (root_id, id) is different from two separate indexes (root_id) and (id). The combined one resolves different problems. – The Impaler Apr 21 '18 at 14:35
Thanks, i hope it will at least partially solve the problem. – AlexGr Apr 21 '18 at 20:19

Long Sql Query optimization

2 Answers2