RE: [PHP] PHP/mySQL question about groups
You are correct, but that is what I meant by all columns (all those in the query, and therefore subject to the distinct). At this point different database engines work differently, without an order by, some will use the primary sequence, some will scan table space (in other words, without an order by [or group by], some engines will give you a performance hit). It's always good practice to write your queries to help your DB engine find an index. Warren -Original Message- From: Andrew Ballard [mailto:[EMAIL PROTECTED] Sent: Thursday, February 07, 2008 7:16 AM To: PHP General list Subject: Re: [PHP] PHP/mySQL question about groups On Feb 7, 2008 1:20 AM, Warren Vail [EMAIL PROTECTED] wrote: I did some looking into performance issues many years ago at company that developed and marketed another database server, comparing the query plan to the actual code, and a query plan usually shows the processes that consume the major amount of time, disk I/O, index or table searches and such, but doesn't show time consumed comparing, discriminating, and totaling, mostly because they are negligible. On the other hand distinct depends on comparison of all columns and will have no help in reducing row counts unless accompanied by an order by clause, where as group by implys an orderby and can be faster if indexes are available for use in row ordering, and while the same totaling occurs, comparison is limited to the columns specified in the group by. Does DISTINCT really compare all columns? I would think it would only compare the columns explicitly included in the SELECT clause. The biggest impact on one or the other would be a well placed index, but for the most part they should be about the same. Warren Vail I have seen discussions where in GROUP BY can be faster than DISTINCT depending on whether the query uses things like correlated subqueries, but this is not applicable in the current case. At any rate, I don't want to stray the conversation any further away than I already have. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/mySQL question about groups
On Feb 7, 2008 1:20 AM, Warren Vail [EMAIL PROTECTED] wrote: I did some looking into performance issues many years ago at company that developed and marketed another database server, comparing the query plan to the actual code, and a query plan usually shows the processes that consume the major amount of time, disk I/O, index or table searches and such, but doesn't show time consumed comparing, discriminating, and totaling, mostly because they are negligible. On the other hand distinct depends on comparison of all columns and will have no help in reducing row counts unless accompanied by an order by clause, where as group by implys an orderby and can be faster if indexes are available for use in row ordering, and while the same totaling occurs, comparison is limited to the columns specified in the group by. Does DISTINCT really compare all columns? I would think it would only compare the columns explicitly included in the SELECT clause. The biggest impact on one or the other would be a well placed index, but for the most part they should be about the same. Warren Vail I have seen discussions where in GROUP BY can be faster than DISTINCT depending on whether the query uses things like correlated subqueries, but this is not applicable in the current case. At any rate, I don't want to stray the conversation any further away than I already have. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/mySQL question about groups
On Feb 6, 2008 8:46 PM, Rob Gould [EMAIL PROTECTED] wrote: Let's say I have a PHP-based wine application, and it's taking a set of mySQL data that looks like this: wineidname size 123 Silver Oak 750ML 123 Silver Oak 1.5L 123 Silver Oak 1.5L 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 1.5L i think you can just do group by wineid, size -nathan
RE: [PHP] PHP/mySQL question about groups
Select * from wine where name = 'Silver Oak' GROUP BY wineid,size bastien Date: Wed, 6 Feb 2008 17:46:52 -0800 From: [EMAIL PROTECTED] To: php-general@lists.php.net Subject: [PHP] PHP/mySQL question about groups Let's say I have a PHP-based wine application, and it's taking a set of mySQL data that looks like this: wineid name size 123 Silver Oak 750ML 123 Silver Oak 1.5L 123 Silver Oak 1.5L 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 1.5L If I do a: Select * from wine where name = 'Silver Oak' GROUP BY 'wineid' I'd get: Silver Oak However, what I'd REALLY like to return is: Silver Oak 750ML Silver Oak 1.5L I'd like the groupby to group by wineid, BUT ALSO separate the groups by 'size'. So there'd be a '750ML' group, and a '1.5L' group Can anyone tell me how I'd do that? I'm hoping I don't have to write a PHP script that loops through the results and separates things manually. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _
Re: [PHP] PHP/mySQL question about groups
On Feb 6, 2008 8:46 PM, Rob Gould [EMAIL PROTECTED] wrote: Let's say I have a PHP-based wine application, and it's taking a set of mySQL data that looks like this: wineidname size 123 Silver Oak 750ML 123 Silver Oak 1.5L 123 Silver Oak 1.5L 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 1.5L If I do a: Select * from wine where name = 'Silver Oak' GROUP BY 'wineid' I'd get: Silver Oak However, what I'd REALLY like to return is: Silver Oak 750ML Silver Oak 1.5L I'd like the groupby to group by wineid, BUT ALSO separate the groups by 'size'. So there'd be a '750ML' group, and a '1.5L' group Can anyone tell me how I'd do that? I'm hoping I don't have to write a PHP script that loops through the results and separates things manually. That's something MySQL will allow that IMO it should not. Being able to use SELECT * and GROUP BY at the same time can create confusion as it did here. The other suggestions would probably work, but a good rule of thumb is not to use any columns in the SELECT clause unless they are either included in the GROUP BY clause or else use one of the aggregate functions like COUNT, SUM, AVG, etc. (I'm not sure of the actual SQL standard on this point, but SQL Server insists on it.) SELECT name, size FROMwine GROUP BY name, size Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROMwine Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/mySQL question about groups
On Feb 6, 2008 10:59 PM, Andrew Ballard [EMAIL PROTECTED] wrote: Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROMwine im not sure why, but i think distinct is typically way slower than group by. -nathan
Re: [PHP] PHP/mySQL question about groups
On Feb 6, 2008 11:20 PM, Nathan Nobbe [EMAIL PROTECTED] wrote: On Feb 6, 2008 10:59 PM, Andrew Ballard [EMAIL PROTECTED] wrote: Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROMwine im not sure why, but i think distinct is typically way slower than group by. -nathan I can't really say for MySQL, but in my experience I'd say it depends. It seems to me that for this case they should be about the same, as it's always been my thinking that GROUP BY did a DISTINCT implicitly. However, I don't really know the internals of any DB platform so I can't confirm that. I ran DESCRIBE on a couple different tables, and they both return the same plan. I don't see any notable difference in the queries either. However, I'll leave it to the OP to test and see if one is better for his question. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP/mySQL question about groups
I did some looking into performance issues many years ago at company that developed and marketed another database server, comparing the query plan to the actual code, and a query plan usually shows the processes that consume the major amount of time, disk I/O, index or table searches and such, but doesn't show time consumed comparing, discriminating, and totaling, mostly because they are negligible. On the other hand distinct depends on comparison of all columns and will have no help in reducing row counts unless accompanied by an order by clause, where as group by implys an orderby and can be faster if indexes are available for use in row ordering, and while the same totaling occurs, comparison is limited to the columns specified in the group by. The biggest impact on one or the other would be a well placed index, but for the most part they should be about the same. Warren Vail -Original Message- From: Andrew Ballard [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 06, 2008 8:41 PM To: PHP General list Subject: Re: [PHP] PHP/mySQL question about groups On Feb 6, 2008 11:20 PM, Nathan Nobbe [EMAIL PROTECTED] wrote: On Feb 6, 2008 10:59 PM, Andrew Ballard [EMAIL PROTECTED] wrote: Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROMwine im not sure why, but i think distinct is typically way slower than group by. -nathan I can't really say for MySQL, but in my experience I'd say it depends. It seems to me that for this case they should be about the same, as it's always been my thinking that GROUP BY did a DISTINCT implicitly. However, I don't really know the internals of any DB platform so I can't confirm that. I ran DESCRIBE on a couple different tables, and they both return the same plan. I don't see any notable difference in the queries either. However, I'll leave it to the OP to test and see if one is better for his question. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
