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...
Add index to your reference fields I mean columns which in
ON part like
LEFT JOIN og_companies c ON r.client_id = c.id LEFT JOIN og_rating_types t ON r.rating_type_id = t.id LEFT JOIN og_actions a ON r.pacra_action = a.id LEFT JOIN og_outlooks o ON r.pacra_outlook = o.id LEFT JOIN og_lterms l ON r.pacra_lterm = l.id LEFT JOIN og_sterms s ON r.pacra_sterm = s.id LEFT JOIN pacra_client_opinion_relations pr ON pr.opinion_id = c.id LEFT JOIN pacra_clients pc ON pc.id = pr.client_id LEFT JOIN city ON city.id = pc.head_office_id
so how can you add an index on mysql?
ALTER TABLE your_table_name ADD INDEX (your_column_name);
ALSO for better performance your join columns should have the same structure. For example if your column_1 is int(11) unsigned other side should be same.
- Make certain you have indexes on all columns that are part of table joins
- Make certain you have indexes on any column used in a filter
- Explicitly define primary keys Explicitly define foreign key relationships
- For large data sets, use table partitioning Define columns as NOT NULL where possible
Below fields should be indexed to get performance-
Table : og_ratings notification_date pacra_action pacra_outlook pacra_lterm pacra_sterm
You may try with individual or combined index by which you get better performance.
If you show all tables structure with index details then can help you better.
Update for Index creation:
alter table og_ratings add index idx_pacra_action(pacra_action), add index idx_pacra_outlook(pacra_outlook), add index idx_pacra_lterm(pacra_lterm), add index idx_pacra_sterm(pacra_sterm);
2nd Edit due to change in query logic:
Actually your query is logically is not correct as you are tying to pass 2 parameters in not in clause like
where (client_id, notification_date) not in (SELECT client_id, MAX(notification_date) FROM og_ratings GROUP BY client_id)
So check below query if it provides you your desired results and should be fast-
SELECT r.client_id,c.id,t.id,a.id,o.id,c.name AS opinion, r.notification_date, t.title AS ttitle,a.title AS atitle,o.title AS otitle, l.title AS ltitle, s.title AS stitle, r.opinion_id, pc.id, r.pr_client_id AS pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title AS cname FROM (SELECT a.client_id, a.notification_date, a.rating_type_id, a.pacra_action, a.pacra_outlook, a.pacra_lterm, a.pacra_sterm, a.opinion_id, a.pr_client_id FROM (SELECT t.client_id, t.notification_date, t.rating_type_id, t.pacra_action, t.pacra_outlook, t.pacra_lterm, t.pacra_sterm, pr.opinion_id, pr.client_id AS pr_client_id, CASE WHEN @category != t.client_id THEN @rownum := 1 ELSE @rownum := @rownum + 1 END AS rank, @category := t.client_id AS var_category FROM og_ratings t JOIN pacra_client_opinion_relations pr ON pr.opinion_id = r.client_id JOIN (SELECT @rownum := NULL, @category := '') r WHERE pr.client_id = 50 ORDER BY t.client_id,t.notification_date DESC) a WHERE x.rank=2) r LEFT JOIN og_companies c ON r.client_id = c.id LEFT JOIN og_rating_types t ON r.rating_type_id = t.id LEFT JOIN og_actions a ON r.pacra_action = a.id LEFT JOIN og_outlooks o ON r.pacra_outlook = o.id LEFT JOIN og_lterms l ON r.pacra_lterm = l.id LEFT JOIN og_sterms s ON r.pacra_sterm = s.id LEFT JOIN pacra_clients pc ON pc.id = r.pr_client_id LEFT JOIN city ON city.id = pc.head_office_id
As I have not executed this query, so if you get any syntax error etc then you can create a sqlfiddle so that I can correct it.