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 > > >
