merge queries to fastest answer

Question

i have to show status of system to end user: so i have a set of query that show sent data in 1min,5min,hour,day,month intervals

$sentCount = new stdClass();
$sentCount->amon = $link->query("select * from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 2073600 ;")->num_rows;
$sentCount->amin = $link->query("select * from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 60 ;")->num_rows;
$sentCount->a5min = $link->query("select * from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 300 ;")->num_rows;
$sentCount->ahour = $link->query("select * from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 3600 ;")->num_rows;
$sentCount->aday = $link->query("select * from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 86400 ;")->num_rows;

but this style of code need a large time to excute how can i change it to get faster answer !?

dont do `select *`. get only relevant columns you need – Madhur Bhaiya Sep 27 '18 at 07:39 — Madhur Bhaiya, Sep 27 '18 at 07:39
you need rows count? than use `COUNT(*)` function in MySQL – Artem Ilchenko Sep 27 '18 at 07:39 — Artem Ilchenko, Sep 27 '18 at 07:39

score 1 · Answer 1 · answered Sep 27 '18 at 07:41

It seems like you need only Row counts. Issues with your current code are following:

If you just want to get the number of rows, you should use inbuilt COUNT() function. It will be much faster.
Secondly, your data packet from Database server to Application code becomes much smaller now. You are not fetching all the row data, instead just a number is coming now.
Also, read Why is SELECT * considered harmful?

Try the following query:

$sentCount = new stdClass();
$sentCount->amon = $link->query("select COUNT(*) as count from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 2073600 ;")->row['count'];
$sentCount->amin = $link->query("select COUNT(*) as count from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 60 ;")->row['count'];
$sentCount->a5min = $link->query("select COUNT(*) as count from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 300 ;")->row['count'];
$sentCount->ahour = $link->query("select COUNT(*) as count from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 3600 ;")->row['count'];
$sentCount->aday = $link->query("select COUNT(*) as count from row_sent where result='succes' and TIMESTAMPDIFF(SECOND, send_date , now()) < 86400 ;")->row['count'];

score 0 · Answer 2 · answered Sep 27 '18 at 07:43

First of you can combine into one query and use IF and SUM to get a result.

SELECT
    *,
    SUM(IF(TIMESTAMPDIFF(SECOND, send_date, NOW()) < 2073600,1,0)) AS MONTH,
    SUM(IF(TIMESTAMPDIFF(SECOND, send_date, NOW()) < 60,1,0)) AS one_min,
    SUM(IF(TIMESTAMPDIFF(SECOND, send_date, NOW()) < 300,1,0)) AS five_min,
    SUM(IF(TIMESTAMPDIFF(SECOND, send_date, NOW()) < 3600,1,0)) AS HOUR,
    SUM(IF(TIMESTAMPDIFF(SECOND, send_date, NOW()) < 86400,1,0)) AS DAY
    FROM
        row_sent
    WHERE
        result = 'succes';

merge queries to fastest answer

2 Answers2