Why my mysql query is working slow?

Question

I have a query which fetch the records of 2nd highest date from db. My query working well but it take too much time in execution. How can i execute my query quickly.

Answer 1

I'm going to take this a bit differently than others... Am I missing something or aside from the obvious indexing optimizations which all your joins are on primary keys to the lookups -- is your criteria accurate?

Here is what I mean... Your final WHERE clause..

   WHERE
         r.client_id IN ( SELECT opinion_id 
                             FROM pacra_client_opinion_relations
                            WHERE client_id = 50 )

You are asking for a CLIENT_ID being in a select result of an OPINION_ID but only looking for opinions for client_ID = 50. What is the context of the Opinion_id.

CLARIFICATION of Client vs Opinion from your table "pacra_client_opinion_relations" Lets look at sample data as below

Opinion_ID    Client_ID   Other...
1             28          ...
2             35          ...
3             50          ...
4             2           ...
5             50          ...
6             6           ...
7             50          ...
8             4           ...

If your query is all OPINION_IDs for client_id = 50, you would return OPINION_ID #s 3, 5 and 7. Since your where clause is asking for the CLIENT_ID IN the select of Opinions, you are now grabbing data for clients 3, 5 and 7 and have NOTHING to do with client #50 you originally started looking at.

Also... if you are looking only for things from "Client_ID = 50", then your prior queries trying to get the SECOND to most recent notification date, you are querying ALL Clients. If you add a where clause for "Client_ID = 50", then you will only get those and not the 2nd to recent notification of ALL clients.

To clarify the MAX() less than the inner MAX(). Ex data from ratings you would get the following...

og_ratings (assuming this data is pre-sorted per client for sample purposes)
client_id  notification_date
13         Sep 5      <- You want THIS entry if it was client 13 included
13         Sep 14     <- Most recent for client 13
28         Sep 1
28         Sep 8
28         Sep 10     <- You want THIS entry if client 28 included
28         Sep 11     <- Most recent for client 28
29         Sep 4      <- You want THIS entry if client 29 included
29         Sep 11     <- Most recent for client 29
43         Sep 16     <- Most recent for client 43 and no prior, 
                         this would never show as only one entry for client
50         Sep 2
50         Sep 9
50         Sep 12     <- You want THIS entry for client 50
50         Sep 15     <- Most recent for client 50

Based on the sample data, you would get... Different clients could have distinctly different 2nd from latest dates

client_id  notification_date
13         Sep 5
28         Sep 10
29         Sep 4 
50         Sep 12

If all you cared about in your OUTERMOST query was client 50, and your actual data had hundreds of clients (or even thousands of clients), you are querying ALL clients. You could limit your inner query specifically for client 50 via...

 og_ratings r INNER JOIN (
    SELECT 
      client_id, 
      max(notification_date) notification_2nd_date
   FROM 
      og_ratings
   WHERE
      (client_id, notification_date) 
         NOT IN ( SELECT client_id, max(notification_date)
                     FROM og_ratings 
                     GROUP BY client_id )
   GROUP BY 
      client_id
   ORDER BY
      client_id DESC

could be adjusted to...

 og_ratings r INNER JOIN (
    SELECT 
      client_id, 
      max(notification_date) notification_2nd_date
   FROM 
      og_ratings
   WHERE
      client_id = 50    <--- ADDED TO WHERE CLAUSE for CLIENT 50 ONLY
      AND (client_id, notification_date) 
         NOT IN ( SELECT client_id, max(notification_date)
                     FROM og_ratings 
                     WHERE client_id = 50    <--- ADDED HERE TOO FOR CLIENT 50
                     GROUP BY client_id )
   GROUP BY 
      client_id
   ORDER BY
      client_id DESC

and it would only return the SINGLE record for client 50 vs dates for ALL clients

client_id  notification_date
50         Sep 12

Lastly, and in many times of offering MySQL queries, I have offered using the keyword STRAIGHT_JOIN. This basically tells MySQL to query in the order you told it to... Sometimes when (such as your case), you have a bunch of lookup tables, it might be trying to think for you and use a lookup table first due to low record counts (or what/however) it applies the query.

SELECT STRAIGHT_JOIN ... rest of query

If I am accurate on my assumptions, a more simplified query could also be done, I am just trying to explain the different pieces that I see as questionable... Finally, as you see the sample data I have, if you could prepare sample data on this and future of what you have and what you are trying to get might help...