3

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
flamePuppy
  • 61
  • 5
  • no. it's not possible. Why do you need to collapse that field into a single result in mysql? you can trivially do this in the PHP code as well, and not have to worry about the group_concat length limits at all. – Marc B Oct 01 '14 at 14:09
  • Thx for the reply...because the table rows are not 1 to 1....COUNT(bid) will return one row whereas the table 2 query can return anywhere from 0 to 1000 rows – flamePuppy Oct 01 '14 at 14:12
  • 1
    so just fetch your table2.cids-as is and do the counting/concatenating in php. – Marc B Oct 01 '14 at 14:13
  • I know I can extract the data and filter with a WHILE loop but I tried this and so far it's really messy, but I'm sure it's possible....just trying to see if I can make my life a little easier – flamePuppy Oct 01 '14 at 14:14
  • group_concat is handy, but it's not a solution to this problem. – Marc B Oct 01 '14 at 14:16
  • @Marc B, so looks like the answer to my question is "No" and I'll have to toil away at filtering my WHILE loop, right? I appreciate your time – flamePuppy Oct 01 '14 at 14:17
  • no, you can still do the filtering in your actual queries, you just can't do the group_concat business at the mysql level. plus, you're using aggregate functions without a `group by` clause, so don't be surprised if you start getting wonky results. – Marc B Oct 01 '14 at 14:17
  • OK....I'll see if I can filter more using GROUP BY. Thanks for your great tips....if I could upvote your comments I would (my rep is 1) :) No wait, it's higher now, but still can't upvote – flamePuppy Oct 01 '14 at 14:23
  • 1
    Seriously, how many microseconds are you expecting to save by avoiding a query with `SET SESSION`? Also, did you even clock the overhead of `GROUP_CONCAT()`? The time to parse the coma-separated value from your application? This is, by far, premature optimisation. – RandomSeed Oct 01 '14 at 15:56

2 Answers2

3

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.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Side note: the `LEFT JOIN` seems to be unnecessary. Is it possible (or even relevant) that a `bid` has no matching `cid`? If not, then use a regular `[INNER] JOIN`, this is often faster. – RandomSeed Oct 02 '14 at 08:00
  • There is oftentimes no matching cid....this requires a LEFT, doesn't it? I though INNER JOIN was for when there is a matching table2.col2 for every table1.col1 – flamePuppy Oct 02 '14 at 15:51
  • This is correct, you do need a `LEFT JOIN` in this case. – RandomSeed Oct 02 '14 at 16:08
2

Here's a trick for you:

SELECT COUNT(table1.bid) AS count,
GROUP_CONCAT(case when mod(table2.cid,3)=0 then table2.cid else null end) AS cidList1,
GROUP_CONCAT(case when mod(table2.cid,3)=1 then table2.cid else null end) AS cidList2,
GROUP_CONCAT(case when mod(table2.cid,3)=2 then table2.cid else null end) 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';
Jaugar Chang
  • 3,176
  • 2
  • 13
  • 23