问题描述:

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 r.client_id, c.id

  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?

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

http://kb.tableau.com/articles/knowledgebase/database-query-performance

网友答案:

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.

相关阅读:
Top