Thanks for the casting tip.
Dan Buettner-2 wrote: > > 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] > > > -- View this message in context: http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8513291 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]
