Help indexing this query.

[Andrew Rosolino]

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

[Dan Buettner-2]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Andrew Rosolino]

--- 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 <[e-mail]> 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=[e-mail]
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=[e-mail]

[Dan Buettner-2]

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@...> 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@...> 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@...
> >>
> >>
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...
> >
> >
> >
>
> --
> 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/mysql?unsub=drbuettner@...
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Andrew Rosolino]

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 <[e-mail]> 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 <[e-mail]> 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=[e-mail]
> >>
> >>
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=[e-mail]
> >
> >
> >
>
> --
> 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/mysql?unsub=[e-mail]
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=[e-mail]