-1

I've an application which reads email messages (I download the emails from gmail inbox in my application's database) from MySql Database. and the Database has following structure

Table1 (Contacts):

ContactID (int)
ContactName (varchar(100)
ContactEmailAddress (varchar(150))

Table2 (Subjects):

SubjectID (int)
ContactID (int)
Subject (varchar(200))

Table3 (Messages):

MessageID (int)
SubjectID (int)
MessageText (varchar(150)
IsRead (tinyint)
IsReceived (tinyint)
MessageDate (DateTime)

And here is my query to fetch most recent 40 records

SELECT * FROM(SELECT ROW_NUMBER()OVER(Order by isRead ASC,MessageDate DESC) RecID,
c.ContactName,s.subject,s.SubjectID,d.MessageDate,d.isRead
from Contacts c                    
INNER JOIN Subjects s on s.ContactID=c.ContactID
JOIN (
select MAX(MessageID) dtl_id,SubjectID from Messages where IsReceived=1    
GROUP BY SubjectID)d_max on (d_max.subjectid=s.subjectid)
JOIN Messages d on (d.MessageID=d_max.dtl_id)
) AS RowConstrainedResult where RecID >=1 and RecID <=40  ORDER BY RecID

but this query takes almost 15 seconds to load. What should be done to improve the query performance. as my all primary key columns and referenced key columns are indexed. And the Messages table has almost 500k records in it.

Rick James
  • 135,179
  • 13
  • 127
  • 222
Meva159
  • 19
  • 3
  • Show table schemas with data types, indexes, and execution plan. – underscore_d Mar 26 '21 at 11:55
  • I've updated the datatypes, I can't share the table schemas here. All the integer fields are indexed. and the execution plan can be seen here https://ibb.co/pPFFJpZ – Meva159 Mar 26 '21 at 12:33
  • Please explain the goal of the query. And provide `EXPLAIN SELECT ...`. And provide `SHOW CREATE TABLE` (we need to see the indexes, etc). – Rick James Mar 26 '21 at 22:22
  • `show index from tablename` can you show output with detailed explain plan – ROHIT KHURANA Mar 29 '21 at 03:08
  • @RickJames the goal of the query is to get recent 40 unread mails. I will try to explain, assuming we've 2 Email addresses in contacts table, email1@mydomain.com and email2@yourdomain.com. and both of them have 10 messages in Messages table. now suppose email2@yourdomain.com has the second message with UnRead status in DB, while the emial1 has the 1st message with UnRead status in DB, so the email1 will be shown at top. the the explain result can be viewed at https://ibb.co/FbPs3SD – Meva159 Mar 29 '21 at 10:44

1 Answers1

1

The EXPLAIN in the link you provided does not seem to refer to the SELECT you provided, so I have to ignore it.

This index may be helpful:

Messages:  (IsReceived, SubjectID, MessageID)

I added the tag [groupwise-maximum]. It links to a lot of other Questions that are doing similar things. Very few actually succeed in optimizing the task. I have a comparison of the faster-than-most techniques here: http://mysql.rjweb.org/doc.php/groupwise_max

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • thanks, adding the suggested index reduced the query execution time from 12 seconds to under 5 seconds.. – Meva159 Apr 01 '21 at 09:49
  • 5 sec is still terrible for a UI. Check out my link and/or the tag. – Rick James Apr 01 '21 at 17:20
  • Yes you are right, but still better than 12 or 13 seconds. I did check the link, and it seems I will have to make some minor changes in my DB architecture to bring it down to 2 to 3 seconds. – Meva159 Apr 02 '21 at 06:13