CODEWITHSUNDEEP
mysqlsqlquery-performance
Ryukish
Ryukish

Reputation: 100

SQL Query taking far too long

I'm using MySQL and I have a table with above 30 000 rows which I'm querying to use later for a table on a page. The problem is that the query takes more than 1 second to perform and I'm in need of speeding it up since the users of the website are just constantly complaining about it.

Here's the query:

     SELECT `partners`.`id`, 
            `partners`.`first_name`, 
            `partners`.`url`, 
            `partners`.`country`, 
            `partners`.`city`, 
            `partners`.`followers` AS `amount_of_followers`, 
            `partner_user_brand`.`brand_id` AS `brand`, 
            MAX(posts.code_start) AS code_start, 
            MAX(posts.code_end) AS code_end, 
            `partners`.`platforms`, 
            (    SELECT sales 
                   FROM posts 
                  WHERE partner_id = partners.id 
               ORDER BY posts.code_end DESC 
                  LIMIT 1
            ) AS latest_sale, 
            `partners`.`email`, 
            `partners`.`gender`, 
            `partner_brand_status`.`status_id`, 
            `partners`.`school`, 
            (       SELECT COALESCE(SUM(sales), 0) 
                      FROM posts 
                     WHERE partner_id = partners.id
            ) AS tsales, 
            COALESCE(MAX(posts.sales), 0) AS best_sale, 
            IF(partner_user_brand.brand_id=1, RIGHT(contacted, 10), RIGHT(nv_contacted, 10)) AS last_contact 
       FROM `partners` 
  LEFT JOIN `posts` 
         ON `partners`.`id` = `posts`.`partner_id` 
 INNER JOIN `partner_user_brand` 
         ON `partners`.`id` = `partner_user_brand`.`partner_id` 
 INNER JOIN `partner_brand_status` 
         ON `partners`.`id` = `partner_brand_status`.`partner_id` 
        AND `partner_user_brand`.`brand_id` = `partner_brand_status`.`brand_id` 
   GROUP BY `partners`.`id`, 
            `partner_user_brand`.`brand_id`

Is there a way to shorten it up or make it faster?

Also, is there fast way of counting the results for the query?

Upvotes: 0

Views: 96

Answers (2)

user359135
user359135

Reputation: 

select 
    a.* 
    ,posts.sales as LatestSale
from ( 
SELECT `partners`.`id`, 
        `partners`.`first_name`, 
        `partners`.`url`, 
        `partners`.`country`, 
        `partners`.`city`, 
        `partners`.`followers` AS `amount_of_followers`, 
        `partner_user_brand`.`brand_id` AS `brand`, 
        MAX(posts.code_start) AS code_start, 
        MAX(posts.code_end) AS code_end, 
        `partners`.`platforms`, 
        `partners`.`email`, 
        `partners`.`gender`, 
        `partner_brand_status`.`status_id`, 
        `partners`.`school`, 
        SUM(posts.sales) AS tsales, 
        COALESCE(MAX(posts.sales), 0) AS best_sale, 
        IF(partner_user_brand.brand_id=1, RIGHT(contacted, 10), RIGHT(nv_contacted, 10)) AS last_contact 
   FROM `partners` 
LEFT JOIN `posts` 
     ON `partners`.`id` = `posts`.`partner_id` 
INNER JOIN `partner_user_brand` 
     ON `partners`.`id` = `partner_user_brand`.`partner_id` 
INNER JOIN `partner_brand_status` 
     ON `partners`.`id` = `partner_brand_status`.`partner_id` 
    AND `partner_user_brand`.`brand_id` = `partner_brand_status`.`brand_id` 
  GROUP BY `partners`.`id`, 
        `partner_user_brand`.`brand_id`) as a 
left outer join posts 
on a.id = posts.partner_id
 and a.code_end = posts.code_end

This may help as it stop you running a query to get last sale for every row, instead it does most of your query then joins to posts again just to get that latest post value - joining on the id AND the latest post code_end value already established for the code_end column

But also review execution plan etc. and sorry if syntax is a little off t-sql is my day-to-day

in summary thinking of the logical execution of the statement: if you have sub query (select value from ..) before the FROM in your main query then that sub query will get executed after your main query and for every row returned by your main query. my modification will still execute you main query first then join it to the posts table, but it will only do that once (afaik) for all rows rather than once per row. - but this is just what i would do by default in this case, check execution plan for accurate info

Upvotes: 0

Ashish Rajput
Ashish Rajput

Reputation: 1529

You can look into below stuff and that can be helpful in performance :

Execution Plan : Look into your query execution plan. This will be the best starting points for performance point of view For more on execution plan

Indexes : You can increase the performance by adding indexes on the columns on which you are making joins or the columns in the where section.

Paging : How you are serving the data is equally important. Users are not going to view 3000 rows in a shot. So you can add paging to improve the performance

Reformat the query : Apart from above points remove the multiple select in the single query. Try to use joins for that

Upvotes: 1

Related Questions