SQL Multiple Queries - Too Heavy?

Question

I've been coding a new dashboard, where I want to show various DB stats, mainly counting rows and setting the results into variables, its all working correctly, but I am a bit concerned that so many SELECT queries could become too heavy if a lot of users are entering or refreshing the page.

Appreciate your input :)

$tbl_players = players;

$xbox = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'XBOX360'");
$ps3 = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'PS3'");
$pc = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'PC'");
$xbfa = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'XBOX360' && fathread LIKE 'http%'");
$psfa = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'PS3' && fathread LIKE 'http%'");
$pfa = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'PC' && fathread LIKE 'http%'");
$xbcr = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'XBOX360' && crthread LIKE 'http%'");
$pscr = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'PS3' && crthread LIKE 'http%'");
$pcr = mysql_query("SELECT * FROM $tbl_players WHERE Console = 'PC' && crthread LIKE 'http%'");
while($row = mysql_fetch_array($xbox))
    {
$cxbox = mysql_num_rows($xbox);
}
while($row = mysql_fetch_array($ps3))
    {
$cps3 = mysql_num_rows($ps3);
}
while($row = mysql_fetch_array($pc))
    {
$cpc = mysql_num_rows($pc);
}
while($row = mysql_fetch_array($xbfa))
    {
$xboxfa = mysql_num_rows($xbfa);
}
while($row = mysql_fetch_array($psfa))
    {
$ps3fa = mysql_num_rows($psfa);
}
while($row = mysql_fetch_array($pfa))
    {
$pcfa = mysql_num_rows($pfa);
}
while($row = mysql_fetch_array($xbcr))
    {
$xboxcr = mysql_num_rows($xbcr);
}
while($row = mysql_fetch_array($pscr))
    {
$ps3cr = mysql_num_rows($pscr);
}
while($row = mysql_fetch_array($pcr))
    {
$pccr = mysql_num_rows($pcr);
}

$tbl_interactive = interactive;

$maximum = mysql_query("SELECT ID FROM $tbl_interactive ORDER BY ID DESC LIMIT 1");
while($max = mysql_fetch_array($maximum))
{
$cint = $max['ID'];
}
$xboxf = mysql_query("SELECT * FROM $tbl_interactive WHERE Console = 'XBOX360' && Type = 'Player' && $date < EndTime");
$xboxc = mysql_query("SELECT * FROM $tbl_interactive WHERE Console = 'XBOX360' && Type = 'Club' && $date < EndTime");
$ps3f = mysql_query("SELECT * FROM $tbl_interactive WHERE Console = 'PS3' && Type = 'Player' && $date < EndTime");
$ps3c = mysql_query("SELECT * FROM $tbl_interactive WHERE Console = 'PS3' && Type = 'Club' && $date < EndTime");
$pcf = mysql_query("SELECT * FROM $tbl_interactive WHERE Console = 'PC' && Type = 'Player' && $date < EndTime");
$pcc = mysql_query("SELECT * FROM $tbl_interactive WHERE Console = 'PC' && Type = 'Club' && $date < EndTime");
while($row = mysql_fetch_array($xboxf))
    {
$xboxfi = mysql_num_rows($xboxf);
}
while($row = mysql_fetch_array($xboxc))
    {
$xboxci = mysql_num_rows($xboxc);
}
while($row = mysql_fetch_array($ps3f))
    {
$ps3fi = mysql_num_rows($ps3f);
}
while($row = mysql_fetch_array($ps3c))
    {
$ps3ci = mysql_num_rows($ps3c);
}
while($row = mysql_fetch_array($pcf))
    {
$pcfi = mysql_num_rows($pcf);
}
while($row = mysql_fetch_array($pcc))
    {
$pcci = mysql_num_rows($pcc);
}

Many Thanks!

Answer 1

Yes, that is too many queries, way too much overhead, and too much heavy lifting to get the resultset you want.

You could get all nine of those counts from the $tbl_players table with a single statement, and on a single row. Those nine separate statements you have are preparing nine separate resultsets, and you're fetching all those rows in the resultset to the client just to get a count. (Maybe there's some optimization in there somewhere, but MySQL is preparing all those rows to return to the client.) It would be much more efficient to have MySQL just return the counts you want, using a statement something like this:

SELECT SUM(IF(p.Console = 'XBOX360' ,1,0))                             AS xbox
     , SUM(IF(p.Console = 'PS3'     ,1,0))                             AS ps3
     , SUM(IF(p.Console = 'PC'      ,1,0))                             AS pc
     , SUM(IF(p.Console = 'XBOX360' AND p.fathread LIKE 'http%' ,1,0)) AS xbfa
     , SUM(IF(p.Console = 'PS3'     AND p.fathread LIKE 'http%' ,1,0)) AS psfa
     , SUM(IF(p.Console = 'PC'      AND p.fathread LIKE 'http%' ,1,0)) AS pfa
     , SUM(IF(p.Console = 'XBOX360' AND p.crthread LIKE 'http%' ,1,0)) AS xbfr
     , SUM(IF(p.Console = 'PS3'     AND p.crthread LIKE 'http%' ,1,0)) AS pscr
     , SUM(IF(p.Console = 'PC'      AND p.crthread LIKE 'http%' ,1,0)) AS pcr
  FROM $tbl_players p

That gets you back a single row, with nine values. That would cut down on the amount of code you've got considerably, and significantly improve performance.

Similarly, you can get all those counts from the $tbl_interactive table with a single statement as well:

SELECT SUM(IF(t.Console = 'XBOX360' AND t.Type = 'Player' ,1,0)) AS xbfi
     , SUM(IF(t.Console = 'XBOX360' AND t.Type = 'Club'   ,1,0)) AS xbci
     , SUM(IF(t.Console = 'PS3'     AND t.Type = 'Player' ,1,0)) AS psfi
     , SUM(IF(t.Console = 'PS3'     AND t.Type = 'Club'   ,1,0)) AS psci
     , SUM(IF(t.Console = 'PC'      AND t.Type = 'Player' ,1,0)) AS pcfi
     , SUM(IF(t.Console = 'PC'      AND t.Type = 'Club'   ,1,0)) AS pcci
  FROM $tbl_interactive t
 WHERE t.Console IN ('XBOX360','PS3','PC')
   AND t.Type IN ('Player','Club')
   AND $date < t.EndTime

---

while($row = mysql_fetch_array($tbl_interactive_counts))
{
    $xboxfi = $row["xbfi"];
    $xboxci = $row["xbci"];
    $ps3fi  = $row["psfi"];
    $ps3ci  = $row["psci"];
    $pcfi   = $row["pcfi"];
    $pcci   = $row["pcci"];
}

---

FOLLOWUP:

It's possible that the SQL statements above to return a NULL rather than a zero, when there are no rows that satisfy the predicates (that is, match the WHERE clause). To have the statement return a count of zero in place of the NULL, we can use the MySQL IFNULL() function as convenient shorthand:

SELECT IFNULL(SUM(IF(t.Console = 'XBOX360' AND t.Type = 'Player' ,1,0)),0) AS xbfi
     , IFNULL(SUM(IF(t.Console = 'XBOX360' AND t.Type = 'Club'   ,1,0)),0) AS xbci
     , IFNULL(SUM(IF(t.Console = 'PS3'     AND t.Type = 'Player' ,1,0)),0) AS psfi
     , IFNULL(SUM(IF(t.Console = 'PS3'     AND t.Type = 'Club'   ,1,0)),0) AS psci
     , IFNULL(SUM(IF(t.Console = 'PC'      AND t.Type = 'Player' ,1,0)),0) AS pcfi
     , IFNULL(SUM(IF(t.Console = 'PC'      AND t.Type = 'Club'   ,1,0)),0) AS pcci
  FROM $tbl_interactive t
 WHERE t.Console IN ('XBOX360','PS3','PC')
   AND t.Type IN ('Player','Club')
   AND $date < t.EndTime

Note that we've just wrapped an expression: IFNULL(expr,0)

That's just a shorthand equivalent to: IF(expr IS NULL, 0, expr)

also equivalent to the ANSI standard: CASE WHEN expr IS NULL THEN 0 ELSE expr END

(Using IFNULL, we only have to specify expr one time.)

---

Or, you could let MySQL return the NULL, and handle the replacment of the NULL with a zero in PHP. (Note that fetched value in the $row array will still contain the NULL, but a zero will be assigned to the scalar:

$xboxfi = is_null($row["xbfi"]) ? 0 : $row["xbfi"] ;

Answer 2

Many CMS systems and online shops run 50 or more queries at one refresh.

Anyway - in your case - I see some optimization possibilities. For example - you can read information about many types and consoles in one query, then do some work on processing/filtering returned data in PHP. Maybe you should make some easy and medium exercises in PHP (arrays, result fetching, foreach loops) and a little MySQL training. It will help you a lot. I think most time-efficient educational materials are at The New Boston or PHP Academy. There are many valuable video tutorials.

I will not give you complete solution (it could take too much time), Im not sure what is final purpose of your queries, but you may start with consolidating them into one query.

Maybe like this?

SELECT * FROM $tbl_interactive 
WHERE 
    Console IN('XBOX360', 'PS3', 'PC') && 
    Type in('Player','Club') && 
    $date < EndTime

If you want to get only number of rows - you should not use * and count resultset rows count in PHP.

Use one field or count(*) function. Adding GROUP BY on some fields, and adding these fields to result (after or before count) will divide your count results into few rows containing few options, and counts of rows mathing your groups.

Count and group by example:

SELECT Type, Console, count(*) FROM $tbl_interactive 
WHERE 
    Console IN('XBOX360', 'PS3', 'PC') && 
    Type in('Player','Club') && 
    $date < EndTime
GROUP BY Type, Console

of course you have to read resultset, not number of returned rows.

Try it in MySQL workbench or phpmyadmin to see how it works.

Answer 3

Since you are just counting the number of entries in each category, you could eliminate most of the queries by using COUNT and GROUP BY. An example:

$tbl_players_result = mysql_query("SELECT Console, COUNT(*) AS item_count FROM $tbl_players GROUP BY Console");
while($row = mysql_fetch_assoc($tbl_players_result))
{
    /* Testing... */
    print($row["Console"] . " - " . $row["item_count"] . " items");
}

You can do the same thing for the queries with additional conditions (fathread, crthread).

Also, you only need to call mysql_num_rows once per result, so you don't need those while loops either.

Answer 4

I would make sure you have proper indices (indexes if you prefer) in place.

Also you might want to use COUNT instead of selecting lots of rows and then using only the row count. This is because a query like this:

SELECT * FROM $tbl_players WHERE Console = 'XBOX360'

... is sending back lots of data, and since you're not using it, that's wasted bandwidth and unnecessary extra loading time. Better go with:

SELECT COUNT(*) FROM $tbl_players WHERE Console = 'XBOX360'

...and then put the proper PHP in place to retrieve only the count.

Answer 5

Depending on what you have in your table, you may be able to use an aggregate query to get the information you need.

SELECT COUNT(*) `total`, Console, fathread 
FROM $tbl_players
GROUP BY Console, fathread

Answer 6

You are sending 9 requests to the server which requires 9 round trips. Why not just use a single UNION ALL and a single request? Not knowing enough about your program, it's safe to say you may want to consider some redesign.