SELECT count from multiple tables between 2 dates with BIG DATA

Question

I have multiple tables (same column structures) which I want to have the count rows between 2 date ranges. Right now it gives me all records from each table. Each table have around 700K records, which makes it also slow to search through. I'm not getting the right query for this. The below query is what I have so far.

$monday = '2018-01-15';
$tuesday = '2018-01-16';

SELECT count(datetime) FROM uptime_m1 WHERE datetime 
BETWEEN '$monday' and'$tuesday' and status = 'idle' 
UNION ALL 
SELECT (datetime) FROM uptime_m2 WHERE datetime 
BETWEEN '$monday' and'$tuesday' and status = 'idle';

PHP CODE:

if ($result = $mysqli->query("
SELECT count(datetime) FROM uptime_m1 WHERE datetime 
   BETWEEN '$monday' and'$tuesday' and status = 'idle' 
UNION ALL 
SELECT (datetime) FROM uptime_m2 WHERE datetime 
   BETWEEN '$monday' and'$tuesday' and status = 'idle';
")) {
   $row_cnt = $result->num_rows;
   $count_m1_idle_monday = $row_cnt;
   $result->close();
}

If all you need is the count you could use `SELECT count(datetime) FROM ...` instead. — Felippe Duarte, Jan 22 '18 at 16:21
Adding an index to the `datetime` column should speed up the query (I think). Side note: You might want to read about using statements in PHP, which can make your life easier when trying to write a query. — Tim Biegeleisen, Jan 22 '18 at 16:22
@FelippeDuarte: I had tried the count() but still the same.. — Marios, Jan 22 '18 at 16:24
`SELECT count(datetime) as whatever_alias` - try an alias. Also, `$Tuesday` != `$tuesday`. You should have gotten an error about that, being an undefined variable, should that be your real code. — Funk Forty Niner, Jan 22 '18 at 16:25
You should a) show us the actual PHP code you used to run the query, and b) ideally show us the raw MySQL query being run. It may not be what you think. — Tim Biegeleisen, Jan 22 '18 at 16:25
@FunkFortyNiner: the capital T was done by the editor here on stackoverflow. The variables are okay now. — Marios, Jan 22 '18 at 16:33

ebahi · Answer 1 · 2018-01-22T16:39:15.547

0

You can use a subrequest to count each table and sum the result.

select
  (select count(*) from uptime_m1
   where datetime between '$monday' and '$tuesday' and status = 'idle')
+ (select count(*) from uptime_m2
   where datetime between '$monday' and '$tuesday' and status = 'idle')
as nbrows;

edited Jan 22 '18 at 16:39

answered Jan 22 '18 at 16:26

ebahi

536
2
7

SELECT count from multiple tables between 2 dates with BIG DATA

1 Answers1