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
