Re: Help indexing this query. - Dan Buettner - com.mysql.lists.mysql

5 messages in com.mysql.lists.mysqlRe: Help indexing this query.

From	Sent On	Attachments
altendew	22 Jan 2007 09:15
Dan Buettner	22 Jan 2007 09:20
altendew	22 Jan 2007 09:40
Dan Buettner	22 Jan 2007 12:28
altendew	22 Jan 2007 16:40

Subject:	Re: Help indexing this query.
From:	Dan Buettner (drbu...@gmail.com)
Date:	01/22/2007 12:28:53 PM
List:	com.mysql.lists.mysql

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

On 1/22/07, altendew <and...@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.