Andrew, couple of suggestions: 1 - where you use s.status='2' change it to s.status=2 otherwise MySQL is likely casting your data from int to string, which is slow and also precludes using an index.
2 - in this case, instead of using a left join, try using a subquery: WHERE ... AND s.mid NOT IN (SELECT mid FROM ptsContestExclude) - or - change your index around, from UNIQUE (cid,mid) to UNIQUE (mid,cid) due to the way MySQL uses indices you need the queried-upon column(s) listed first(earlier) in the index. These might speed things up HTH, Dan On 1/22/07, altendew <[EMAIL PROTECTED]> wrote:
--- EXPLAIN --- 1 SIMPLE e system cid NULL NULL NULL 0 const row not found 1 SIMPLE s ref sid,status,timeframe status 4 const 20438 Using where; Using temporary; Using filesort 1 SIMPLE m eq_ref PRIMARY,status PRIMARY 4 sc_72.s.mid 1 Using where --- members --- id int(20) No first varchar(255) No last varchar(255) No username varchar(25) No email varchar(255) No password varchar(25) No PRIMARY id --- ptsContestExclude --- cid int(20) No 0 mid int(20) No 0 UNIQUE (cid,mid) --- ptsSignups (all indexes) --- PRIMARY id INDEX (mid,ptsID) INDEX (status,ptsID) INDEX timeframe (mid, status, time) Dan Buettner-2 wrote: > > Andrew, can you post the result of EXPLAIN <query> for your query? > Minus the "FORCE INDEX" too. Also the structure of the other 2 tables > would be helpful as well. > > Thanks, > Dan > > > > On 1/22/07, altendew <[EMAIL PROTECTED]> wrote: >> >> --- ptsSignups -- >> id int(20) No >> mid int(20) No 0 >> ptsID int(20) No 0 >> pps double No 0 >> points double No 0 >> loginID varchar(255) No >> emailConfirm longtext No >> time timestamp Yes CURRENT_TIMESTAMP >> reasonForDeny longtext No >> status int(1) No 1 >> >> --- index (timeframe) --- >> >> timeframe (mid,status,time) >> >> --- query --- >> >> SELECT SUM(s.pps) as earned,m.id,m.username >> FROM ptsSignups s >> FORCE INDEX(timeframe) >> JOIN members m >> ON s.mid=m.id >> AND m.status='Member' >> LEFT JOIN ptsContestExclude e >> ON e.cid=1 >> AND e.mid=m.id >> WHERE >> s.status='2' >> AND s.time>=2004-06-08 >> AND s.time<2008-06-08+INTERVAL 1 DAY >> AND e.mid IS NULL >> GROUP BY s.mid >> HAVING earned>0 >> ORDER BY earned DESC >> >> --- problem --- >> >> `ptsSignups` is a table listing everything my members have completed. >> Sometimes I like to run contests to see who has earned the most. >> `members` >> is a table that contains all my users. `ptsContestExclude` is a table of >> members of whom I would like to exclude from the contest. >> >> What I do first is group the table `ptsSignups` by member id, and >> calculate >> a sum of how much they earned. Then I reorder that sum in Descending >> order >> so the highest earned is on top. >> >> This `ptsSignups` table contains 82752 rows and is 75KB big. It runs >> extremely slow. I tried to create an index for it but it failed to >> increase >> performance. >> >> Any help is appreciated. >> -- >> View this message in context: >> http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554 >> Sent from the MySQL - General mailing list archive at Nabble.com. >> >> >> -- >> 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] > > > -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505966 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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]
