Harrison Fisk <[EMAIL PROTECTED]> writes:
>The difference between the count(*) and the other query is that the
>real query has to use the datafile to retrieve the data when you are
>involving the actual columns. With the count(*) query it is using an
>Index only read, meaning that it doesn't have to the use the datafile
>at all to resolve it. If you do an EXPLAIN on the count(*) query, you
>should see a 'Using Index' in the Extra column. So it is using the
>index and estimating it is going to have to read 3885524 rows from the
>data file. Assuming the estimate is close, that will be an extra
>3885524 disk seeks and reads to find the data for your query. That is
>why vmstat is showing the query doing much more disk i/o.
Harrison,
Thanks for helping me to better understand what the explain was
telling me. Though I'm not sure it quite fits with the other data
I collected from the strace of the mysqld that servicing my query. It
looked to be doing a sequential pread, based on the record size of
9 bytes. This was one reason I felt the query was doing a table
scan to fulfill the query.
One interesting experiment I did was to try to do a summation
query with and without an index. The query with an index too 31 hrs.
While the same data set without the index took 7 hours.
>The only way you could improve this is to make a combined index across
>(member_id, pts_awarded) and get rid of the only (member_id) index.
>Then MySQL would be able to again use only the index to resolve the
>query. Keep in mind this would increase your index size by about a
>third, so it would take more diskspace and you would fit less into
>cache, so it would decrease response times slightly for the count(*)
>query.
Thank you for this suggestion. I've dropped the original index
and have added the composite index to the table. It increased the
size of the index file by about 50% as expected, but the summation
query seems to be doing a much better job at scanning the data in
the index rather than in the index/table combination. We'll see
how long this summation query takes at this point.
Thank you for your help and explanations,
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
