SEO

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

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

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

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

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 <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
>>
>>
>
> --
> 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#a8513291
Sent from the MySQL - General mailing list archive at Nabble.com.