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.
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...