Mysql – How to speed up an sql query that round trips between two tables

MySQLperformancePHPquery-performance

I first posted this question on stackoverflow, and now I see this site, seems more relevant.

These tables are not that big, but the time taken to process this query is long.

What can I do with the following queries to speed up the process taken to return results?

$chat_alg = mysqli_query($con, "
    SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time > NOW() - INTERVAL 3 DAY && sid != '' 
    GROUP BY sid
    ORDER BY COUNT(*) DESC
") or die(mysqli_error($con));

while($row_chat_alg = mysqli_fetch_array($chat_alg)) {
    $chart_songs[] = $row_chat_alg['sid'];
}

$imploded_songs = implode($chart_songs, ',');

$projects = $dbh->query("
       SELECT * FROM music
       WHERE perms != 'c' && sid IN($imploded_songs) 
       ORDER BY FIELD(sid,$imploded_songs) 
       limit 50
");

...

This tables are growing, I'm worried that maintaining them will be a problem if this is already slow.

Best Answer

From my (rather limited) knowledge of php, I assume you want to combine the two queries into one:

SELECT m.*, p.frequency 
FROM 
  ( SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time > NOW() - INTERVAL 3 DAY 
      AND sid <> '' 
    GROUP BY sid
    ORDER BY COUNT(*) DESC
    LIMIT 50 
  ) AS p
  JOIN music AS m
    ON m.sid = p.sid
WHERE m.perms <> 'c' 
ORDER BY m.frequency DESC ;

As for efficiency, it's better to tune the first query separately and then this combined one.

An index on plays (sid, time) should help for the first query. For the second, if there is an index on music (sid), it should be quite efficient as it's a join to only 50 rows of music.

Related Solutions

Mysql – How to speed up thesql query

Here is your original query

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM school
    INNER JOIN `student-` ON `school`.`scid`   = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `degree_o`.`scid` = `accredit`.`scid`
    ORDER BY `school`.`ARCO_name` ASC LIMIT 0, 25
;

Two Suggestions

Perform LIMIT 0, 25 on school table before the INNER JOINs
INNER JOIN school.scid to the other three(3) tables

Here is my propsed query

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM
    (SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
    INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
    INNER JOIN `degree_o` ON `school`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `school`.`scid` = `accredit`.`scid`
;

Give it a Try !!!

UPDATE 2012-06-25 12:13 EDT

To speed up the subquery, make sure you have this index:

ALTER TABLE school ADD INDEX ARCO_name_scid_ndx (ARCO_name,scid);

UPDATE 2012-06-25 12:18 EDT

AS pointed out by @yercube in his comments to my answer, you may want to attach the student-id from to degree_o and degree_id over to accedit. Perhaps something like this:

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM
    (SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
    INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`studend_id` = `degree_o`.`studend_id`
    INNER JOIN `accredit` ON `degree_o`.`degree_id` = `accredit`.`degree_id`
;

Mysql – Speed up a MySQL BETWEEN query

In your query, the join has no conditions. Shouldnt be something like SELECT xxx FROM table1 JOIN table2 ON table1.column = table2.column and xxx.date between xxx to xxx .

Add: Here is the explain for the test table

explain select find from table1 t1 join table2 t2 on t1.find between t2.from1 and t2.to1;
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | t2    | index | from1,to1,from_to | from_to | 12      | NULL |    2 | Using index                                    |
|  1 | SIMPLE      | t1    | ALL   | idx               | NULL    | NULL    | NULL |    5 | Range checked for each record (index map: 0x1) |