Group By query optimization

2005-07-15 Thread Kishore Jalleda 
Hi All,
  I have a mysql query which takes 8 seconds to run ona dual
xeon 2.4, 3Gig ram box,
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 

table   typepossible_keys   key key_len ref rowsExtra
gamesessionsALL NULLNULLNULLNULL915522  Using where; 
Using
temporary; Using filesort

How can I optimize the query 
Thanks 
Kishore

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Group By query optimization

2005-07-15 Thread Andrew Braithwaite 
Hi,

Put indexes on 'valid' and 'sessiontype' and all will be good.

Cheers,

Andrew


On 15/7/05 18:26, Kishore Jalleda [EMAIL PROTECTED] wrote:

 Hi All,
   I have a mysql query which takes 8 seconds to run ona dual
 xeon 2.4, 3Gig ram box,
 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
 
 table  type  possible_keys  key  key_len  ref  rows  Extra
 gamesessions  ALL  NULL  NULL  NULL  NULL  915522  Using where; Using
 temporary; Using filesort
 
 How can I optimize the query
 Thanks 
 Kishore
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]