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 <andrew@shiftcode.com> 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 <andrew@shiftcode.com> 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-....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/mysql?unsub=drbuettner@gmail.com
> >>
> >>
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=lists@nabble.com
> >
> >
> >
>
> --
> View this message in context: http://www.nabble.com/Help-indexing-....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/mysql?unsub=drbuettner@gmail.com
>
>