Optimizing GROUP BY and ORDER BY
I have a query: SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE ( Country != 'USA' AND Country != 'US' AND Country != 'Unit' AND Country != 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP BY Country ORDER BY Cnt DESC LIMIT 8 that gets the top 8 non-US countries from the properties table. There is about 500,000 rows in the properties table. This is a costly query: ++-++---+---+-+-+--+---+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+--+---+---+ | 1 | SIMPLE | properties | range | Country | Country | 7 | NULL | 74602 | Using where; Using index; Using temporary; Using filesort | ++-++---+---+-+-+--+---+---+ 1 row in set (0.00 sec) Any ideas on how to get rid of the Using temporary; Using filesort or do this in a better way with PHP? Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing GROUP BY and ORDER BY
On Fri, Jul 25, 2008 at 12:27 AM, Michael Stearne [EMAIL PROTECTED] wrote: I have a query: SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE ( Country != 'USA' AND Country != 'US' AND Country != 'Unit' AND Country != 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP BY Country ORDER BY Cnt DESC LIMIT This is a costly query I suggest that this is not a well normalized. I suggest that at a minium you should be using a 'Country' which would include a distinct entry for each country. If such a table exists a column could be added which would store a count of number of rows in the Properties table that reference the record in the Country table. This count could be maintained through your application, or via triggers if you are using MySQL 5.0 . -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing GROUP BY and ORDER BY
On Fri, Jul 25, 2008 at 12:35 PM, Arthur Fuller [EMAIL PROTECTED] wrote: ORDER BY implies a sort of the result set. I don't think there is any way around that. I guess so. What I am doing is to just run the query once per day and store the results in memcache. Michael Arthur On Fri, Jul 25, 2008 at 4:27 AM, Michael Stearne [EMAIL PROTECTED] wrote: I have a query: SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE ( Country != 'USA' AND Country != 'US' AND Country != 'Unit' AND Country != 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP BY Country ORDER BY Cnt DESC LIMIT 8 that gets the top 8 non-US countries from the properties table. There is about 500,000 rows in the properties table. This is a costly query: ++-++---+---+-+-+--+---+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+--+---+---+ | 1 | SIMPLE | properties | range | Country | Country | 7 | NULL | 74602 | Using where; Using index; Using temporary; Using filesort | ++-++---+---+-+-+--+---+---+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing GROUP BY
Hi All, I have a query which takes approximately 0.5 seconds to execute , it is as follows SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; EXPLAIN SELECT gives me this, also there is an index on the columns valid and sessiontype id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gamesessions ref valid_sess valid_sess 1 const 55003 Using where; Using temporary; Using filesort Can this be optimized to run faster Appreciate your time Kishore Jalleda
Re: Optimizing GROUP BY
Kishore Jalleda [EMAIL PROTECTED] wrote on 10/13/2005 02:25:52 PM: Hi All, I have a query which takes approximately 0.5 seconds to execute , it is as follows SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; EXPLAIN SELECT gives me this, also there is an index on the columns valid and sessiontype id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gamesessions ref valid_sess valid_sess 1 const 55003 Using where; Using temporary; Using filesort Can this be optimized to run faster Appreciate your time Kishore Jalleda There are several index changes you could make to optimize this particular query. The problem is, if we create too many indexes your INSERT time will begin to tank and your disk space will become scarce. This section of the manual definitely applies to your situation: http://dev.mysql.com/doc/refman/4.1/en/group-by-optimization.html and more generally http://dev.mysql.com/doc/refman/4.1/en/query-speed.html This section of the manual may also be able to help: http://dev.mysql.com/doc/refman/4.1/en/optimizing-database-structure.html in particular, read http://dev.mysql.com/doc/refman/4.1/en/indexes.html and http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html The problem is, we can make that one query VERY fast at the expense of all sorts of other things. Query optimization is an art, a balancing act. You have to know what you are giving up when you ask for certain performance increases. I just don't have enough information about your overall query patterns to make an informed decision on which optimization will work for this particular query without penalizing other queries in the process. What you have to be able to answer is: How important is the speed of this one query compared to everything else going on in the database as a whole. Nobody can tell you that but you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Optimizing GROUP BY
That was an excellent reply, I always see you helping so many people, keep the great work going .. Sincerely, Kishore Jalleda On 10/13/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Kishore Jalleda [EMAIL PROTECTED] wrote on 10/13/2005 02:25:52 PM: Hi All, I have a query which takes approximately 0.5 seconds to execute , it is as follows SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; EXPLAIN SELECT gives me this, also there is an index on the columns valid and sessiontype id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gamesessions ref valid_sess valid_sess 1 const 55003 Using where; Using temporary; Using filesort Can this be optimized to run faster Appreciate your time Kishore Jalleda There are several index changes you could make to optimize this particular query. The problem is, if we create too many indexes your INSERT time will begin to tank and your disk space will become scarce. This section of the manual definitely applies to your situation: http://dev.mysql.com/doc/refman/4.1/en/group-by-optimization.html and more generally http://dev.mysql.com/doc/refman/4.1/en/query-speed.html This section of the manual may also be able to help: http://dev.mysql.com/doc/refman/4.1/en/optimizing-database-structure.html in particular, read http://dev.mysql.com/doc/refman/4.1/en/indexes.html and http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html The problem is, we can make that one query VERY fast at the expense of all sorts of other things. Query optimization is an art, a balancing act. You have to know what you are giving up when you ask for certain performance increases. I just don't have enough information about your overall query patterns to make an informed decision on which optimization will work for this particular query without penalizing other queries in the process. What you have to be able to answer is: How important is the speed of this one query compared to everything else going on in the database as a whole. Nobody can tell you that but you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Optimizing GROUP BY
Hi! I've found this in: http://www.mysql.com/information/presentations/presentation-oscon2000-2719/ Instead of doing a lot of |GROUP BY|s on a big table, create summary tables of the big table and query this instead. Would you please tell me how to create summary tables that can help me get this query info: mysql select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; This is the table info: mysql describe traf_oper; ++--+---+--+-+-+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+-+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+-+---+ Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
