Distinct problem

wilku

I have a problem with DISTINCT. I don't know if you can say to mysql: find me distinct this column and that but return info from other columns as well (from the first row according to order by). To elaborate more

First the tables:
products, - stores product information
products_lang, - language dependent fields
localization,- shops
products_localizations_xref, - connects products and shops
promotions, - only products that have certain promotion are displayed
products_categories_xref_search, - we can say what category the product is in
firms, - a firm can have many localizations
address - stores parts of the address (street, numer, flat etc.)

the query I have now:

if (!empty($name)) $name_sql = " AND (products_lang.name like '%$name%' OR products_lang.short_name like '%$name%' )";
		else $name_sql = "";

$sql_prototype = 'select DISTINCT products.id as pid,localization.id as lid,localization.x as fx,
		localization.y as fy,products.picture as img,products.price_before as pbefore,
		products.price_after as pafter, promotions.date_stop as valid, products_lang.name as fullname,
		products_lang.short_name as shortname,  products_lang.description as pdescription,
		firms.name as fname, address.code as fcode, address.city as fcity, address.street as fstreet,
		address.number as fnumber, address.flat as fflat, localization.tel as ftel,
		localization.fax as ffax, localization.www as fwww, localization.email as femail
		, POW( localization.x - '.$x.', 2 ) + POW( localization.y -'.$y.', 2 ) AS distsq 
from products,products_lang,localization,products_localizations_xref,promotions,products_categories_xref_search,firms,address
where products_localizations_xref.product_id = products.id 
and products_localizations_xref.localization_id = localization.id
and localization.id = promotions.sub_id
and promotions.type = 4
AND promotions.date_start <= CURDATE( )
AND promotions.date_stop >= CURDATE( )
AND x > '.($x-$radius).'
AND x <'.($x+$radius).'
AND y > '.($y-$radius).'
AND y <'.($y+$radius).'
AND firms.auth_id = localization.auth_id
AND localization.address = address.id
AND products_lang.product_id = products.id
AND products_lang.lang = \''.$_SESSION['language'].'\'
'.$name_sql.'
AND products_categories_xref_search.product_id = products.id
AND products_categories_xref_search.category_id = \'_CAT_\'
ORDER BY distsq
';

What it does is basically select all products with a given name or category which belong to localization within the square of 2x radius centered at $x,$ y, and that localization has to have certain promotion to be searched.
If a product belongs to two localizations DISTINCT doesnt treat those rows as the same (because localization data is different). What I would like to achieve is to select distinct products.id and the rest info should go from the localization closest to $x, $y.
I tried to find something about distinct in mysql doc but it's not as userfriendly as php doc. Any ideas?

wilku

Bump
I really need to do this. The only thing that comes to me is to split this into two queries:

$sql_prototype = 'select DISTINCT products.id as pid
from products,products_lang,localization,products_localizations_xref,promotions,products_categories_xref_search,firms,address
where products_localizations_xref.product_id = products.id
and products_localizations_xref.localization_id = localization.id
and localization.id = promotions.sub_id
and promotions.type = 4
AND promotions.date_start <= CURDATE( )
AND promotions.date_stop >= CURDATE( )
AND x > '.($x-$radius).'
AND x <'.($x+$radius).'
AND y > '.($y-$radius).'
AND y <'.($y+$radius).'
AND firms.auth_id = localization.auth_id
AND localization.address = address.id
AND products_lang.product_id = products.id
AND products_lang.lang = \''.$_SESSION['language'].'\'
'.$name_sql.'
AND products_categories_xref_search.product_id = products.id
AND products_categories_xref_search.category_id = \'_CAT_\'
LIMIT '.$offset.','.$count;

which will give me distinct product id's and then 10 times sql to choose the closest location for each product (as $count is 10).

$sql_prototype = 'select products.id as pid,localization.id as lid,localization.x as fx,
        localization.y as fy,products.picture as img,products.price_before as pbefore,
        products.price_after as pafter, promotions.date_stop as valid, products_lang.name as fullname,
        products_lang.short_name as shortname,  products_lang.description as pdescription,
        firms.name as fname, address.code as fcode, address.city as fcity, address.street as fstreet,
        address.number as fnumber, address.flat as fflat, localization.tel as ftel,
        localization.fax as ffax, localization.www as fwww, localization.email as femail
        , POW( localization.x - '.$x.', 2 ) + POW( localization.y -'.$y.', 2 ) AS distsq
from products,products_lang,localization,products_localizations_xref,promotions,products_categories_xref_search,firms,address
where 
products.id = '.$product_id_found_in_the_firs_query.'
and products_localizations_xref.product_id = products.id
and products_localizations_xref.localization_id = localization.id
and localization.id = promotions.sub_id
and promotions.type = 4
AND promotions.date_start <= CURDATE( )
AND promotions.date_stop >= CURDATE( )
AND x > '.($x-$radius).'
AND x <'.($x+$radius).'
AND y > '.($y-$radius).'
AND y <'.($y+$radius).'
AND firms.auth_id = localization.auth_id
AND localization.address = address.id
AND products_lang.product_id = products.id
AND products_lang.lang = \''.$_SESSION['language'].'\'
'.$name_sql.'
AND products_categories_xref_search.product_id = products.id
AND products_categories_xref_search.category_id = \'_CAT_\'
ORDER BY distsq
LIMIT 0,1
';

But this seams horribly unoptimized. Is this the only way?

mpb001

It doesn't really get enforced but sql is more readable if it is written seperating the layers of the consistent from the inconsistent.

The consistent part is the relational design of the database eg.

"AND localization.address = address.id
AND products_lang.product_id = products.id" etc as it will always be the same.

Replace those using the join syntax
http://dev.mysql.com/doc/refman/5.0/en/join.html

It also keeps them bunched together all the time so things don't end up going
"
and localization.id = promotions.sub_id //relational
and promotions.type = 4 //non relational and dynamic
AND promotions.date_start <= CURDATE( ) //non relational
AND promotions.date_stop >= CURDATE( ) //non relational
AND x > '.($x-$radius).' //non relational
AND x <'.($x+$radius).' //non relational
AND y > '.($y-$radius).' //non relational
AND y <'.($y+$radius).' //non relational
AND firms.auth_id = localization.auth_id // relational
AND localization.address = address.id // relational
AND products_lang.product_id = products.id"// relational

SQL should be kept easy to read as when it goes wrong it can really go wrong. Aliasing can also be used so the repetition of the table name in full for every column can be shortened to something less dominating. Eg products_localizations could become pl.

Not having a dig just trying to give you less of a headache in the long run. Once something is clearer than the solution becomes clearer 🙂

mpb001

"and promotions.type = 4 //non relational and dynamic"

should be replaced with and

"promotions.type = 4 //non relational and potentially dynamic as it can be run for other promotions types"

Can't edit. Going around in circles being logged out all the time 🙁
Could be my browser but I haven't got time.

wilku

Ok, so cleaning it up a bit

if (!empty($name)) $name_sql = " AND (products_lang.name like '%$name%' OR products_lang.short_name like '%$name%' )";
        else $name_sql = "";

$sql_prototype = 'select DISTINCT products.id as pid,localization.id as lid,localization.x as fx,
        localization.y as fy,products.picture as img,products.price_before as pbefore,
        products.price_after as pafter, promotions.date_stop as valid, products_lang.name as fullname,
        products_lang.short_name as shortname,  products_lang.description as pdescription,
        firms.name as fname, address.code as fcode, address.city as fcity, address.street as fstreet,
        address.number as fnumber, address.flat as fflat, localization.tel as ftel,
        localization.fax as ffax, localization.www as fwww, localization.email as femail
        , POW( localization.x - '.$x.', 2 ) + POW( localization.y -'.$y.', 2 ) AS distsq
FROM products
JOIN products_lang ON products_lang.product_id = products.id 
JOIN products_localizations_xref ON products_localizations_xref.product_id = products.id
JOIN localization ON  products_localizations_xref.localization_id = localization.id
JOIN promotions ON localization.id = promotions.sub_id
JOIN firms ON firms.auth_id = localization.auth_id
JOIN  address ON localization.address = address.id
JOIN products_categories_xref_search ON products_categories_xref_search.product_id = products.id
WHERE 
promotions.type = 4
AND promotions.date_start <= CURDATE( )
AND promotions.date_stop >= CURDATE( )
AND x > '.($x-$radius).'
AND x <'.($x+$radius).'
AND y > '.($y-$radius).'
AND y <'.($y+$radius).'
AND products_lang.lang = \''.$_SESSION['language'].'\'
'.$name_sql.'
AND products_categories_xref_search.category_id = \'_CAT_\'
ORDER BY distsq
';

I'll stay clear of aliases - I'd rather write long names, then mix up what I've shortened by what.
But still this haven't solved my problem. I thought about GROUP BY products.id and GROUP_CONCAT with ORDER BY for each field I need, but it seems worse even then 11 queries 😉 - I don't need details for every row but for the first according to distance. Any help?. I'm on the edge of letting this go with 11 queries, though I don't know how my db server would handle this (as for the lack of funds it shares it's resources with www server and gis server)

wilku

Ok. I've found about temporary tables:

CREATE TEMPORARY TABLE MyTemporaryTable SELECT * FROM MyRealTable WHERE id<10;

The problem with what I showed in #2 was that mysql had to build the same table 11 times. Now I'll try to build a temporary table out of that and perform other operations there.
You think that should be enough for speed optimizing?

mpb001

Should be fine. Although there is also the query cache or innodb equivelent

MyISAM
http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
"Note:

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed."

innodb
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html
innodb_buffer_pool_size

So depending on how frequently those tables tables are updated it keeps a copy of results for later use.

( sometimes a distinct is a group by )
http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html
http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html

11 simple queries can have less resource use than large join creating temporary tables due to the time needed to write data( it can get into GB's after a while).
Also most of those queries could rely on fairly static data that can almost permanently stay in cache.

Also those queries are often useful to have later by being easily callable if wrapped well.

It is a balancing act, between your brain and eyes against server performance. There is the ability to log slow queries in mysql so you can run explains on them etc to check indexes( they can run from memory ). I would set up the caching/memory etc as a start if you haven't to a large enough number. Just for general use they are good things as it is possibly to get down to 1 in 10000 key read request froms the drive.

Worth reading in full..
http://dev.mysql.com/doc/refman/5.0/en/optimization.html