CODEWITHSUNDEEP
phpmysql
Clinton Jooooones
Clinton Jooooones

Reputation: 1027

Why does this MySQL query take forever to run?

Table1 is only 1mb and table2 is 10mb but the query takes at least 30 seconds to load. Is it because of the SUM and COUNT functions?

$index = mysql_query("CREATE index table1_timestamp on table1(timestamp)");
$result = mysql_query("SELECT
    table1.user_id,
    table1.timestamp,
    COUNT(DISTINCT table1.call_id) as calls,
    SUM(table2.recording_file_duration) / 60 as minutes
FROM
    table1
JOIN table2 ON
    table1.call_id = table2.id
WHERE
    table1.user_id = '244'
    AND
    table1.timestamp >= '2013-07-01' and table1.timestamp < '2013-08-01'
GROUP BY
    table1.user_id,
    DAY(table1.timestamp)
ORDER BY
    table1.timestamp DESC");

EDIT: I just checked and table 2 has 300,000 rows. Is that why?

Upvotes: 0

Views: 135

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53830

Add an index that your WHERE clause can utilize for table1. Also, add an index that the JOIN can use for table2.

Add the following indexes:

table1(user_id, timestamp) // Order is important here, ranges go last
table2(id)

Of course, if table2.id is your primary key, you don't need to add that index.

To improve the results even more, you could add covering indexes:

table1(user_id, timestamp, call_id)
table2(id, recording_file_duration)

If you are using InnoDB and any of those columns are primary keys, remove them from the index.

Upvotes: 1

Related Questions