Se7sNator Se7sNator - 19 days ago 7
PHP Question

MySQL query to join more than 3 tables takes way too long

I have about 20 tables, all of them has the same structure as follows:

id | date | received_calls|answered_calls
---|----------------------------------------
1 | 2016-11-10 | 2151 | 2021 |
2 | 2016-11-11 | 5201 | 5121 |
3 | 2016-11-12 | 3333 | 2930 |


what i'm trying to do is sum
section1.received_calls
+
section2.received_calls
+
section3.received_calls
.....(with some formulas as shown on code)...etc at the date of X
and the same again.. in the next row, for the next day date.
and so on.
the dates are unique per table and in daily order and cannot be duplicated.

my code is:

SELECT
`section1`.date AS date,
(
`section1`.received_calls + `section2`.received_calls + `section3`.received_calls
) AS received_calls,
(
`section1`.answered_calls + `section2`.answered_calls + `section3`.answered_calls
) AS answered_calls,
ROUND(
(
`section1`.answered_calls + `section2`.answered_calls + `section3`.answered_calls
) / (
`section1`.received_calls + `section2`.received_calls + `section3`.received_calls
) * (100),2) AS ans_calls,
(
`section1`.Abandoned_Calls + `section2`.Abandoned_Calls + `section3`.Abandoned_Calls
) AS Abandoned_Calls,
ROUND(
(
`section1`.abandoned_calls + `section2`.abandoned_calls + `section3`.abandoned_calls
) / (
`section1`.received_calls + `section2`.received_calls + `section3`.received_calls
) * (100),2) AS aban_calls,
ROUND(
(
`section1`.`Avg_Speed_Ans` + `section2`.`Avg_Speed_Ans` + `section3`.`Avg_Speed_Ans`
) / (
`section1`.received_calls + `section2`.received_calls + `section3`.received_calls
) * (100),2) AS `Avg_Speed_Ans`
FROM
`section1`
JOIN `section2` ON `section1`.date = `section2`.date,
`section3`
GROUP BY
`section1`.date


The problem is that it's working fine, but VERY VERY slow, it can take up 3 hours to complete (and every table has less than 800 row), and I've indexed already the columns i'm using above. and have a primary key set to ID column for each table.

Answer

I have about 20 tables, all of them has the same structure as follows

Your database is not normalized - the first fail.

Did you read any of the many questions here about query performance? Did you note the comments, which ones were upvoted, which were downvoted?

You have not provided the indexes for the tables, you have not provided the explain plan for the query. You have not provided the cardinality of the attributes you use in the join.

can take up 3 hours to complete (and every table has less than 800 row)

This rather implies that the date attribute is not unique, and even after 3 hours, the result you get is wrong - its a partial cartesian product.

This will be much faster and give you a valid answer:

SELECT `date`, SUM(answered_calls), SUM(received_calls)
, SUM(abandoned_calls), /* add in your formulas.... */
FROM
(
SELECT `date`, answered_calls, received_calls, abandoned_calls
FROM section1
UNION ALL
SELECT `date`, answered_calls, received_calls, abandoned_calls
FROM section2
UNION ALL
SELECT `date`, answered_calls, received_calls, abandoned_calls
FROM section3
) AS ilv
GROUP BY ilv.`date`

However this gives you the right answer to the wrong question. It should be constrained by date range, as well as only using a single table.

Comments