CODEWITHSUNDEEP
mysqlinner-joininnodb
tarek nasif
tarek nasif

Reputation: 59

Mysql table join taking too much time, while simple select results fast

I have an InnoDB table daily_sales_msr.

When I run a query from that table without joining, the query returns the output quickly.

But, if I join that table to even with a small table, then it takes too much time. What is the solution to this problem?

For example:

SELECT
sku.ssku,
ROUND(SUM(daily_sales_msr.sale), 3) AS sale,
MONTHNAME(daily_sales_msr.date) AS `month`
FROM
daily_sales_msr
INNER JOIN sku ON sku.id = daily_sales_msr.skid
WHERE
daily_sales_msr.date BETWEEN '2018-08-01'
AND '2018-08-08'
GROUP BY
daily_sales_msr.skid

This query takes more than 1000s.

Without any joining it takes only 0.15s.

Upvotes: 2

Views: 444

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

for performance be sure you have proper index
in your case you could use a composite index on 

table daily_sales_msr for  columns  (skid,date)

and for SQL you are using group by but some of the column in select not in group are not related to aggregation function this in most db engine e in mysql starting form 5.7 is notb alloed by defualt .. the result for these columns is unpredictable so you should add these columns to group by 

SELECT
  sku.ssku,
  ROUND(SUM(daily_sales_msr.sale), 3) AS sale,
  MONTHNAME(daily_sales_msr.date) AS `month`
FROM  daily_sales_msr
INNER JOIN sku ON sku.id = daily_sales_msr.skid
WHERE  daily_sales_msr.date BETWEEN '2018-08-01'  AND '2018-08-08'
GROUP BY  daily_sales_msr.skid, month

or use an aggregation function also for these columns

SELECT
  sku.ssku,
  ROUND(SUM(daily_sales_msr.sale), 3) AS sale,
  MAX(MONTHNAME(daily_sales_msr.date)) AS `month`
FROM  daily_sales_msr
INNER JOIN sku ON sku.id = daily_sales_msr.skid
WHERE  daily_sales_msr.date BETWEEN '2018-08-01'  AND '2018-08-08'
GROUP BY  daily_sales_msr.skid

Upvotes: 1

Related Questions