MySQL query returning limited results

**EasyCo** · Jul 4, 2011 23:47

Rudy, I hope you're out there cause I have a feeling you're the only one who could help me on this one!

This query always worked fine UNTIL the pickup_locations table had a couple hundred rows added to it and the link table product_pickup therefore grew as each product now had more pickup_locations.

Basically some products have over 300 pickup locations and when I run this query I get about 100 returned max. Any help would be appreciated. I have a feeling it might be a memory setting or not?

Consider the following query:

Code MySQL:

SELECT products.id
			, products.title
			, product_locations.title as product_location
			, products.cost
			, products.extra_cost
			, products.extra_cost_note
			, products.saving_note
			, products.picture_url
			, COALESCE(l.locations,"") AS locations
			, COALESCE(e.extras,"")    AS extras
			, products.status
	FROM products
	LEFT OUTER JOIN 
		( SELECT product_pickup.product_id
				, GROUP_CONCAT(pickup_locations.location SEPARATOR "\n") AS locations
	   			FROM product_pickup
			INNER JOIN pickup_locations
				ON pickup_locations.id = product_pickup.pickup_id
			GROUP BY product_pickup.product_id ) AS l
				ON l.product_id = products.id
	LEFT OUTER JOIN
		( SELECT product_extras.product_id
				, GROUP_CONCAT(extras.title SEPARATOR "\n") AS extras
			FROM product_extras
			INNER JOIN extras
				ON extras.id = product_extras.extras_id
			GROUP BY product_extras.product_id ) AS e
				ON e.product_id = products.id
	LEFT OUTER JOIN
		product_locations on products.product_location = product_locations.id
			WHERE products.status IN ( 1 , 2 )';

**guido2004** · Jul 5, 2011 00:06

Check out the manual. I quote

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

You could also choose to extract all locations without GROUP_CONCAT, but then you'll have to manage the "duplicate" product rows (1 for each location) in your script.

**EasyCo** · Jul 5, 2011 00:17

Thanks Guido! I looked everywhere for something along those lines, just didn't think of the group_concat being the limit.

Can you re-explain what you're suggesting, I don't entirely understand in regards to extracting all locations....

Cheers!

**guido2004** · Jul 5, 2011 00:34

Originally Posted by EasyCo

Can you re-explain what you're suggesting, I don't entirely understand in regards to extracting all locations....

Cheers!

I wrote 'with GROUP_CONCAT' when I wanted to write 'without GROUP_CONCAT'.

Code:

SELECT products.id
            , products.title
            , product_locations.title as product_location
            , products.cost
            , products.extra_cost
            , products.extra_cost_note
            , products.saving_note
            , products.picture_url
            , COALESCE(l.location,"") AS locations
            , COALESCE(e.extras,"")    AS extras
            , products.status
    FROM products
    LEFT OUTER JOIN 
        ( SELECT product_pickup.product_id
                , pickup_locations.location 
          FROM product_pickup
            INNER JOIN pickup_locations
                ON pickup_locations.id = product_pickup.pickup_id
        ) AS l
                ON l.product_id = products.id
    LEFT OUTER JOIN
        ( SELECT product_extras.product_id
                , GROUP_CONCAT(extras.title SEPARATOR "\n") AS extras
            FROM product_extras
            INNER JOIN extras
                ON extras.id = product_extras.extras_id
            GROUP BY product_extras.product_id ) AS e
                ON e.product_id = products.id
    LEFT OUTER JOIN
        product_locations on products.product_location = product_locations.id
            WHERE products.status IN ( 1 , 2 )';

As you can see I eliminated the GROUP_CONCAT from the first subquery. It means that if a product has two pickup locations, now it will have two rows in the query result. All columns of those two rows will have the same values (product id, title, ecc) except for the locations column. So to display the product info like you did before, you'll have to handle this new situation (more than 1 row for 1 product).

**EasyCo** · Jul 5, 2011 01:10

Ah gotcha... Hmm, now I've got dilemma. Either I get the data without group_concat and somehow filter is with PHP or I do two different queries and build an array...

**guido2004** · Jul 5, 2011 01:12

I'd go with the first solution. You have to handle it in your script anyway, so why make a second query?

By the way, there still is the possibility of increasing the max string value for GROUP_CONCAT, isn't there? Did you try it?

**EasyCo** · Jul 5, 2011 01:38

Yah, that's the way I went. I was a bit hesitant modifying default values since they're usually defaulted to a certain value for a reason.

The particular table the group_concat is working on is 65kB so I did 100kB * 1024 and that's the max I'm going with.

I chucked this snippet in my DB connection class.

Code PHP:

//Need to set the buffer higher for group_concat or else the results are truncated
if (!mysqli_query($link, 'SET SESSION group_concat_max_len = 102400;')) {
	$error = 'Error setting group_concat_max_len: ' . mysqli_error($link);
	include $_SERVER['DOCUMENT_ROOT'].'/error.html.php';
	exit();
}