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