Re: Help indexing this query.

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