I run a website with a small number of users that I hope will soon grow. I am using a shared web hosting service, and I cannot change MySQL default settings on the web server. I am trying to minimize queries and have found one recurring pair of queries (occurs every 10 seconds via setTimeout to Ajax call) where GROUP_CONCAT could help reduce the following 2 queries:
$logid = mysqli_real_escape_string($cxn, $_SESSION['login_id']);
$sql1 = mysqli_query($cxn, "SELECT COUNT(bid) AS count FROM table1 WHERE login_id = '$logid'");
$sql2 = mysqli_query($cxn, "SELECT cid FROM table2 WHERE (login_id1 = '$logid' OR login_id2 = '$login_id')");
to one query:
SELECT COUNT(table1.bid) AS count,
GROUP_CONCAT(table2.cid) AS cidList
FROM table1
LEFT JOIN table2
ON (table1.login_id = table2.login_id1 OR table1.login_id = table2.login_id2)
WHERE table1.login_id = '$logid';
The problem is that group_concat_max_len is set by default to 1024, and I called my shared hosting service and I cannot change it. Every table2.cid in the query above is 20 characters, so (including the comma separator) GROUP_CONCAT can only return 48 table2.cid records, but I need to be able to return as many as 1000. I already know that I can change this default limit with a query like this:
SET SESSION group_concat_max_len = 1000000;
but this added query defeats the purpose, because even if the two queries above are condensed to one query, I have now added this latest query, so every time the Ajax call is made two queries are still made (and I haven't saved anything).
Is there some way to cluster the GROUP_CONCAT where you retrieve some of the records (table2.cid) for each GROUP_CONCAT? For example, something like (say for 100 records):
SELECT COUNT(table1.bid) AS count,
GROUP_CONCAT(table2.cid ORDER BY table2.cid LIMIT 0,47) AS cidList1,
GROUP_CONCAT(table2.cid ORDER BY table2.cid LIMIT 48,95) AS cidList2,
GROUP_CONCAT(table2.cid ORDER BY table2.cid LIMIT 96,99) AS cidList3
FROM table1
LEFT JOIN table2
ON (table1.login_id = table2.login_id1 OR table1.login_id = table2.login_id2)
WHERE table1.login_id = '$logid';
By the way I tried a test case and the query fails by adding the LIMIT, because without the LIMIT the correct PHP echoed string is returned and with the LIMIT, an empty PHP echoed string is returned plus error log messag:
PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in
Just issue a regular SELECT table1.bid, table2.cid FROM .... And if bid is never NULL, then SELECT cid will suffice (in this case, COUNT(bid) = COUNT(cid))
Counting bid's and joining the list of cid's in your application layer will be virtually instant. The building of the comma-separated list of cid definitely belongs to this layer anyways.
As for the counting of bid's, this is less obvious. If you really want to have MySQL do the counting and still save one database call, I would rather do something like this:
SELECT COUNT(table1.bid) FROM table1 WHERE blah blah
UNION ALL
SELECT cid FROM table2 WHERE blah blah
This is theory, though. Seriously, do not do that.
On second thought, there actually is a less awkward solution:
SELECT COUNT(table1.bid), table2.cid FROM ...
GROUP BY table2.cid WITH ROLLUP
The last row, where table2.cid is NULL, contains the total count.