Really strange index/speed issues - Page 2

11/09/2007, 18h14

Chris Hemmings wrote:
> Chris Hemmings wrote:
>> Baron Schwartz wrote:
>>> Hi Chris,
>>>
>>> Chris Hemmings wrote:
>>>> Dan Buettner wrote:
>>>>> Chris, a couple of thoughts -
>>>>>
>>>>> First, your index on the section is doing you no good (at this
>>>>> time) since
>>>>> all the values are the same. You may already know that, but
>>>>> thought I'd
>>>>> mention it.
>>>>>
>>>>> Second, my theory on why query #1 is faster - if all your prices
>>>>> range from
>>>>> 1 up, and you're querying for prices greater than 0, then MySQL can
>>>>> just
>>>>> return the first 30 rows after sorting them.
>>>>>
>>>>> The second query, where you are looking for prices greater than 1,
>>>>> MySQL has
>>>>> to sort and then examine a number of rows until it finds enough
>>>>> matching
>>>>> rows (price > 1) to satisfy your query. This likely takes a little
>>>>> bit of
>>>>> time. How many rows do you have with price = 1? It would have to
>>>>> scan over
>>>>> that many before it could start satisfying your query, if you think
>>>>> about
>>>>> it.
>>>>>
>>>>> HTH,
>>>>> Dan
>>>>>
>>>>>
>>>>>
>>>>> On 9/10/07, Chris Hemmings <lists@cheeky.org> wrote:
>>>>>> Hello,
>>>>>>
>>>>>> I have a table, currently holding 128,978 rows... In this table,
>>>>>> I have a
>>>>>> section column (int) and a price column (int). Every row has a
>>>>>> section of
>>>>>> 1
>>>>>> currently, every row has a price, ranging from 1 to 10,000.
>>>>>>
>>>>>> I have an index on both columns separately.
>>>>>>
>>>>>> Have a look at these two queries, can someone tell me why there is
>>>>>> such a
>>>>>> difference in speed of execution? (Note difference in price
>>>>>> qualifier)
>>>>>>
>>>>>> ########################################
>>>>>>
>>>>>> SELECT *
>>>>>> FROM `table1`
>>>>>> WHERE price >0
>>>>>> AND section =1
>>>>>> ORDER BY price
>>>>>> LIMIT 0 , 30
>>>>>>
>>>>>> Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec)
>>>>>>
>>>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL
>>>>>> 96734
>>>>>> Using
>>>>>> where; Using filesort
>>>>>>
>>>>>> ########################################
>>>>>>
>>>>>> SELECT *
>>>>>> FROM `table1`
>>>>>> WHERE price >1
>>>>>> AND section =1
>>>>>> ORDER BY price
>>>>>> LIMIT 0 , 30
>>>>>>
>>>>>>
>>>>>> Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec)
>>>>>>
>>>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL
>>>>>> 96734
>>>>>> Using
>>>>>> where; Using filesort
>>>>>>
>>>>>> ########################################
>>>>>>
>>>>>> Other info:
>>>>>>
>>>>>> Query cacheing = off
>>>>>> MySQL version = 5.0.32
>>>>>> OS = Debian Sarge
>>>>>>
>>>>>> Sure, the second query returns 29 fewer records than the first,
>>>>>> but should
>>>>>> that make the difference in time?
>>>>>>
>>>>>> Hope you can shed some light onto this :-)
>>>>>>
>>>>>> Ta!
>>>>>>
>>>>>> Chris.
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> MySQL General Mailing List
>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>> To unsubscribe:
>>>>>> http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>>>>>>
>>>>>>
>>>>>
>>>>
>>>> Thanks Dan,
>>>>
>>>> I've got you on the section index... I was going to use that later,
>>>> when I get somre real data in there.
>>>>
>>>> Anyway, I agree with your logic, but, the inverse is happening.
>>>> The one where it has to actually exclude some rows (because price>1)
>>>> is actually faster. Thats really why this has me baffled, I would
>>>> presume that the price>1 would be slower as it does have to filter
>>>> rows out first.
>>>
>>> There's an easy way to find out: FLUSH STATUS, run the query, SHOW
>>> STATUS LIKE 'handler%'. Do this on an otherwise quiet server if
>>> possible.Or use MySQL Query Profiler -- it does a lot of math for you
>>> :-)Baron
>>>
>>>
>>>
>>>
>>>
>>>
>>
>> Thanks Baron!
>>
>> I think you have hit upon something, doing what you said on a 'silent'
>> server, I get the following:
>>
>> SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price
>> LIMIT 0 , 30;
>>
>> 30 rows in set (0.00 sec)
>>
>> mysql> SHOW STATUS LIKE 'handler%';
>> +----------------------------+-------+
>> | Variable_name | Value |
>> +----------------------------+-------+
>> | Handler_commit | 0 |
>> | Handler_delete | 0 |
>> | Handler_discover | 0 |
>> | Handler_prepare | 0 |
>> | Handler_read_first | 0 |
>> | Handler_read_key | 1 |
>> | Handler_read_next | 29 |
>> | Handler_read_prev | 0 |
>> | Handler_read_rnd | 0 |
>> | Handler_read_rnd_next | 0 |
>> | Handler_rollback | 0 |
>> | Handler_savepoint | 0 |
>> | Handler_savepoint_rollback | 0 |
>> | Handler_update | 0 |
>> | Handler_write | 14 |
>> +----------------------------+-------+
>> 15 rows in set (0.00 sec)
>>
>>
>> SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price
>> LIMIT 0 , 30;
>>
>> 30 rows in set (0.95 sec)
>>
>> mysql> SHOW STATUS LIKE 'handler%';
>> +----------------------------+--------+
>> | Variable_name | Value |
>> +----------------------------+--------+
>> | Handler_commit | 0 |
>> | Handler_delete | 0 |
>> | Handler_discover | 0 |
>> | Handler_prepare | 0 |
>> | Handler_read_first | 0 |
>> | Handler_read_key | 1 |
>> | Handler_read_next | 128978 |
>> | Handler_read_prev | 0 |
>> | Handler_read_rnd | 30 |
>> | Handler_read_rnd_next | 0 |
>> | Handler_rollback | 0 |
>> | Handler_savepoint | 0 |
>> | Handler_savepoint_rollback | 0 |
>> | Handler_update | 0 |
>> | Handler_write | 14 |
>> +----------------------------+--------+
>> 15 rows in set (0.01 sec)
>>
>> So, the slower query obvisouly has the larger Handler_read_next number
>> in it. Looking at the manual, it says the following for that variable:
>>
>> "The number of requests to read the next row in key order. This value
>> is incremented if you are querying an index column with a range
>> constraint or if you are doing an index scan."
>>
>> So, being a bit of a MySQL novice, I can't really see how >0 increases
>> the number of reads by such a VAST amount... Can you guys put this in
>> layman's terms for me?
>>
>> Thanks for the guys :-)
>>
>> Chris.
>>
>
> Hi Guys,
>
> I'm still not having much luck with this, but, I have spotted something
> that maybe I missed earlier.
>
> Doing an explain on both queries again produces the following:
>
> explain select * from table1 where price>0 and section=1 order by price
> limit 0,20;
>
> | 1 | SIMPLE | table1 | range | section,price | section | 4 | NULL
> | 128966 | Using where; Using filesort |
>
> explain select * from table1 where price>1 and section=1 order by price
> limit 0,20;
>
> | 1 | SIMPLE | table1 | range | section,price | price | 4 |
> NULL | 128951 | Using where |
>
> I'm not sure why these 'explains' looks different to the previous
> example. For some reason, the second query (price>1) is using the
> correct key, but on the first query, the wrong key is being used and
> then a filesort has to take place.
>
> Any ideas why this seems to happen, why should the incorrect key be
> selected just by changing the value to qualify on?
>
> I have tried a few more queries that are similar on other tables if you
> would like them.

I suspected something like this was happening, but your earlier EXPLAIN
showed no indexes were being used at all, so I was a little stumped. It
didn't seem to make sense otherwise.

Try a USE INDEX and see if you can get a similar query plan on both queries.

Baron

11/09/2007, 19h21

The results of an EXPLAIN have a lot to do with the data which is
actually on the system. In this case, it seems to hinge on the
distribution of your 'price' attribute.. how many records on your
system? and what is the general distribution of the price attribute?
(how many distinct values)

On 9/11/07, Chris Hemmings <lists@cheeky.org> wrote:
> Chris Hemmings wrote:
> > Baron Schwartz wrote:
> >> Hi Chris,
> >>
> >> Chris Hemmings wrote:
> >>> Dan Buettner wrote:
> >>>> Chris, a couple of thoughts -
> >>>>
> >>>> First, your index on the section is doing you no good (at this time)
> >>>> since
> >>>> all the values are the same. You may already know that, but thought
> >>>> I'd
> >>>> mention it.
> >>>>
> >>>> Second, my theory on why query #1 is faster - if all your prices
> >>>> range from
> >>>> 1 up, and you're querying for prices greater than 0, then MySQL can
> >>>> just
> >>>> return the first 30 rows after sorting them.
> >>>>
> >>>> The second query, where you are looking for prices greater than 1,
> >>>> MySQL has
> >>>> to sort and then examine a number of rows until it finds enough
> >>>> matching
> >>>> rows (price > 1) to satisfy your query. This likely takes a little
> >>>> bit of
> >>>> time. How many rows do you have with price = 1? It would have to
> >>>> scan over
> >>>> that many before it could start satisfying your query, if you think
> >>>> about
> >>>> it.
> >>>>
> >>>> HTH,
> >>>> Dan
> >>>>
> >>>>
> >>>>
> >>>> On 9/10/07, Chris Hemmings <lists@cheeky.org> wrote:
> >>>>> Hello,
> >>>>>
> >>>>> I have a table, currently holding 128,978 rows... In this table, I
> >>>>> have a
> >>>>> section column (int) and a price column (int). Every row has a
> >>>>> section of
> >>>>> 1
> >>>>> currently, every row has a price, ranging from 1 to 10,000.
> >>>>>
> >>>>> I have an index on both columns separately.
> >>>>>
> >>>>> Have a look at these two queries, can someone tell me why there is
> >>>>> such a
> >>>>> difference in speed of execution? (Note difference in price
> >>>>> qualifier)
> >>>>>
> >>>>> ########################################
> >>>>>
> >>>>> SELECT *
> >>>>> FROM `table1`
> >>>>> WHERE price >0
> >>>>> AND section =1
> >>>>> ORDER BY price
> >>>>> LIMIT 0 , 30
> >>>>>
> >>>>> Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec)
> >>>>>
> >>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734
> >>>>> Using
> >>>>> where; Using filesort
> >>>>>
> >>>>> ########################################
> >>>>>
> >>>>> SELECT *
> >>>>> FROM `table1`
> >>>>> WHERE price >1
> >>>>> AND section =1
> >>>>> ORDER BY price
> >>>>> LIMIT 0 , 30
> >>>>>
> >>>>>
> >>>>> Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec)
> >>>>>
> >>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734
> >>>>> Using
> >>>>> where; Using filesort
> >>>>>
> >>>>> ########################################
> >>>>>
> >>>>> Other info:
> >>>>>
> >>>>> Query cacheing = off
> >>>>> MySQL version = 5.0.32
> >>>>> OS = Debian Sarge
> >>>>>
> >>>>> Sure, the second query returns 29 fewer records than the first, but
> >>>>> should
> >>>>> that make the difference in time?
> >>>>>
> >>>>> Hope you can shed some light onto this :-)
> >>>>>
> >>>>> Ta!
> >>>>>
> >>>>> Chris.
> >>>>>
> >>>>>
> >>>>>
> >>>>> --
> >>>>> MySQL General Mailing List
> >>>>> For list archives: http://lists.mysql.com/mysql
> >>>>> To unsubscribe:
> >>>>> http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
> >>>>>
> >>>>>
> >>>>
> >>>
> >>> Thanks Dan,
> >>>
> >>> I've got you on the section index... I was going to use that later,
> >>> when I get somre real data in there.
> >>>
> >>> Anyway, I agree with your logic, but, the inverse is happening. The
> >>> one where it has to actually exclude some rows (because price>1) is
> >>> actually faster. Thats really why this has me baffled, I would
> >>> presume that the price>1 would be slower as it does have to filter
> >>> rows out first.
> >>
> >> There's an easy way to find out: FLUSH STATUS, run the query, SHOW
> >> STATUS LIKE 'handler%'. Do this on an otherwise quiet server if
> >> possible.Or use MySQL Query Profiler -- it does a lot of math for you
> >> :-)Baron
> >>
> >>
> >>
> >>
> >>
> >>
> >
> > Thanks Baron!
> >
> > I think you have hit upon something, doing what you said on a 'silent'
> > server, I get the following:
> >
> > SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price
> > LIMIT 0 , 30;
> >
> > 30 rows in set (0.00 sec)
> >
> > mysql> SHOW STATUS LIKE 'handler%';
> > +----------------------------+-------+
> > | Variable_name | Value |
> > +----------------------------+-------+
> > | Handler_commit | 0 |
> > | Handler_delete | 0 |
> > | Handler_discover | 0 |
> > | Handler_prepare | 0 |
> > | Handler_read_first | 0 |
> > | Handler_read_key | 1 |
> > | Handler_read_next | 29 |
> > | Handler_read_prev | 0 |
> > | Handler_read_rnd | 0 |
> > | Handler_read_rnd_next | 0 |
> > | Handler_rollback | 0 |
> > | Handler_savepoint | 0 |
> > | Handler_savepoint_rollback | 0 |
> > | Handler_update | 0 |
> > | Handler_write | 14 |
> > +----------------------------+-------+
> > 15 rows in set (0.00 sec)
> >
> >
> > SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price
> > LIMIT 0 , 30;
> >
> > 30 rows in set (0.95 sec)
> >
> > mysql> SHOW STATUS LIKE 'handler%';
> > +----------------------------+--------+
> > | Variable_name | Value |
> > +----------------------------+--------+
> > | Handler_commit | 0 |
> > | Handler_delete | 0 |
> > | Handler_discover | 0 |
> > | Handler_prepare | 0 |
> > | Handler_read_first | 0 |
> > | Handler_read_key | 1 |
> > | Handler_read_next | 128978 |
> > | Handler_read_prev | 0 |
> > | Handler_read_rnd | 30 |
> > | Handler_read_rnd_next | 0 |
> > | Handler_rollback | 0 |
> > | Handler_savepoint | 0 |
> > | Handler_savepoint_rollback | 0 |
> > | Handler_update | 0 |
> > | Handler_write | 14 |
> > +----------------------------+--------+
> > 15 rows in set (0.01 sec)
> >
> > So, the slower query obvisouly has the larger Handler_read_next number
> > in it. Looking at the manual, it says the following for that variable:
> >
> > "The number of requests to read the next row in key order. This value is
> > incremented if you are querying an index column with a range constraint
> > or if you are doing an index scan."
> >
> > So, being a bit of a MySQL novice, I can't really see how >0 increases
> > the number of reads by such a VAST amount... Can you guys put this in
> > layman's terms for me?
> >
> > Thanks for the guys :-)
> >
> > Chris.
> >
>
> Hi Guys,
>
> I'm still not having much luck with this, but, I have spotted something
> that maybe I missed earlier.
>
> Doing an explain on both queries again produces the following:
>
> explain select * from table1 where price>0 and section=1 order by price
> limit 0,20;
>
> | 1 | SIMPLE | table1 | range | section,price | section | 4
> | NULL | 128966 | Using where; Using filesort |
>
> explain select * from table1 where price>1 and section=1 order by price
> limit 0,20;
>
> | 1 | SIMPLE | table1 | range | section,price | price | 4 |
> NULL | 128951 | Using where |
>
> I'm not sure why these 'explains' looks different to the previous
> example. For some reason, the second query (price>1) is using the
> correct key, but on the first query, the wrong key is being used and
> then a filesort has to take place.
>
> Any ideas why this seems to happen, why should the incorrect key be
> selected just by changing the value to qualify on?
>
> I have tried a few more queries that are similar on other tables if you
> would like them.
>
> Thanks,
>
> Chris.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>

--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

11/09/2007, 19h56

Baron Schwartz wrote:
> Chris Hemmings wrote:
>> Chris Hemmings wrote:
>>> Baron Schwartz wrote:
>>>> Hi Chris,
>>>>
>>>> Chris Hemmings wrote:
>>>>> Dan Buettner wrote:
>>>>>> Chris, a couple of thoughts -
>>>>>>
>>>>>> First, your index on the section is doing you no good (at this
>>>>>> time) since
>>>>>> all the values are the same. You may already know that, but
>>>>>> thought I'd
>>>>>> mention it.
>>>>>>
>>>>>> Second, my theory on why query #1 is faster - if all your prices
>>>>>> range from
>>>>>> 1 up, and you're querying for prices greater than 0, then MySQL
>>>>>> can just
>>>>>> return the first 30 rows after sorting them.
>>>>>>
>>>>>> The second query, where you are looking for prices greater than
>>>>>> 1, MySQL has
>>>>>> to sort and then examine a number of rows until it finds enough
>>>>>> matching
>>>>>> rows (price > 1) to satisfy your query. This likely takes a
>>>>>> little bit of
>>>>>> time. How many rows do you have with price = 1? It would have
>>>>>> to scan over
>>>>>> that many before it could start satisfying your query, if you
>>>>>> think about
>>>>>> it.
>>>>>>
>>>>>> HTH,
>>>>>> Dan
>>>>>>
>>>>>>
>>>>>>
>>>>>> On 9/10/07, Chris Hemmings <lists@cheeky.org> wrote:
>>>>>>> Hello,
>>>>>>>
>>>>>>> I have a table, currently holding 128,978 rows... In this
>>>>>>> table, I have a
>>>>>>> section column (int) and a price column (int). Every row has a
>>>>>>> section of
>>>>>>> 1
>>>>>>> currently, every row has a price, ranging from 1 to 10,000.
>>>>>>>
>>>>>>> I have an index on both columns separately.
>>>>>>>
>>>>>>> Have a look at these two queries, can someone tell me why there
>>>>>>> is such a
>>>>>>> difference in speed of execution? (Note difference in price
>>>>>>> qualifier)
>>>>>>>
>>>>>>> ########################################
>>>>>>>
>>>>>>> SELECT *
>>>>>>> FROM `table1`
>>>>>>> WHERE price >0
>>>>>>> AND section =1
>>>>>>> ORDER BY price
>>>>>>> LIMIT 0 , 30
>>>>>>>
>>>>>>> Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec)
>>>>>>>
>>>>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL
>>>>>>> 96734
>>>>>>> Using
>>>>>>> where; Using filesort
>>>>>>>
>>>>>>> ########################################
>>>>>>>
>>>>>>> SELECT *
>>>>>>> FROM `table1`
>>>>>>> WHERE price >1
>>>>>>> AND section =1
>>>>>>> ORDER BY price
>>>>>>> LIMIT 0 , 30
>>>>>>>
>>>>>>>
>>>>>>> Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec)
>>>>>>>
>>>>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL
>>>>>>> 96734
>>>>>>> Using
>>>>>>> where; Using filesort
>>>>>>>
>>>>>>> ########################################
>>>>>>>
>>>>>>> Other info:
>>>>>>>
>>>>>>> Query cacheing = off
>>>>>>> MySQL version = 5.0.32
>>>>>>> OS = Debian Sarge
>>>>>>>
>>>>>>> Sure, the second query returns 29 fewer records than the first,
>>>>>>> but should
>>>>>>> that make the difference in time?
>>>>>>>
>>>>>>> Hope you can shed some light onto this :-)
>>>>>>>
>>>>>>> Ta!
>>>>>>>
>>>>>>> Chris.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> MySQL General Mailing List
>>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>>> To unsubscribe:
>>>>>>> http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>> Thanks Dan,
>>>>>
>>>>> I've got you on the section index... I was going to use that
>>>>> later, when I get somre real data in there.
>>>>>
>>>>> Anyway, I agree with your logic, but, the inverse is happening.
>>>>> The one where it has to actually exclude some rows (because
>>>>> price>1) is actually faster. Thats really why this has me
>>>>> baffled, I would presume that the price>1 would be slower as it
>>>>> does have to filter rows out first.
>>>>
>>>> There's an easy way to find out: FLUSH STATUS, run the query, SHOW
>>>> STATUS LIKE 'handler%'. Do this on an otherwise quiet server if
>>>> possible.Or use MySQL Query Profiler -- it does a lot of math for
>>>> you :-)Baron
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> Thanks Baron!
>>>
>>> I think you have hit upon something, doing what you said on a
>>> 'silent' server, I get the following:
>>>
>>> SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price
>>> LIMIT 0 , 30;
>>>
>>> 30 rows in set (0.00 sec)
>>>
>>> mysql> SHOW STATUS LIKE 'handler%';
>>> +----------------------------+-------+
>>> | Variable_name | Value |
>>> +----------------------------+-------+
>>> | Handler_commit | 0 |
>>> | Handler_delete | 0 |
>>> | Handler_discover | 0 |
>>> | Handler_prepare | 0 |
>>> | Handler_read_first | 0 |
>>> | Handler_read_key | 1 |
>>> | Handler_read_next | 29 |
>>> | Handler_read_prev | 0 |
>>> | Handler_read_rnd | 0 |
>>> | Handler_read_rnd_next | 0 |
>>> | Handler_rollback | 0 |
>>> | Handler_savepoint | 0 |
>>> | Handler_savepoint_rollback | 0 |
>>> | Handler_update | 0 |
>>> | Handler_write | 14 |
>>> +----------------------------+-------+
>>> 15 rows in set (0.00 sec)
>>>
>>>
>>> SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price
>>> LIMIT 0 , 30;
>>>
>>> 30 rows in set (0.95 sec)
>>>
>>> mysql> SHOW STATUS LIKE 'handler%';
>>> +----------------------------+--------+
>>> | Variable_name | Value |
>>> +----------------------------+--------+
>>> | Handler_commit | 0 |
>>> | Handler_delete | 0 |
>>> | Handler_discover | 0 |
>>> | Handler_prepare | 0 |
>>> | Handler_read_first | 0 |
>>> | Handler_read_key | 1 |
>>> | Handler_read_next | 128978 |
>>> | Handler_read_prev | 0 |
>>> | Handler_read_rnd | 30 |
>>> | Handler_read_rnd_next | 0 |
>>> | Handler_rollback | 0 |
>>> | Handler_savepoint | 0 |
>>> | Handler_savepoint_rollback | 0 |
>>> | Handler_update | 0 |
>>> | Handler_write | 14 |
>>> +----------------------------+--------+
>>> 15 rows in set (0.01 sec)
>>>
>>> So, the slower query obvisouly has the larger Handler_read_next
>>> number in it. Looking at the manual, it says the following for that
>>> variable:
>>>
>>> "The number of requests to read the next row in key order. This
>>> value is incremented if you are querying an index column with a
>>> range constraint or if you are doing an index scan."
>>>
>>> So, being a bit of a MySQL novice, I can't really see how >0
>>> increases the number of reads by such a VAST amount... Can you guys
>>> put this in layman's terms for me?
>>>
>>> Thanks for the guys :-)
>>>
>>> Chris.
>>>
>>
>> Hi Guys,
>>
>> I'm still not having much luck with this, but, I have spotted
>> something that maybe I missed earlier.
>>
>> Doing an explain on both queries again produces the following:
>>
>> explain select * from table1 where price>0 and section=1 order by
>> price limit 0,20;
>>
>> | 1 | SIMPLE | table1 | range | section,price | section | 4 |
>> NULL | 128966 | Using where; Using filesort |
>>
>> explain select * from table1 where price>1 and section=1 order by
>> price limit 0,20;
>>
>> | 1 | SIMPLE | table1 | range | section,price | price | 4
>> | NULL | 128951 | Using where |
>>
>> I'm not sure why these 'explains' looks different to the previous
>> example. For some reason, the second query (price>1) is using the
>> correct key, but on the first query, the wrong key is being used and
>> then a filesort has to take place.
>>
>> Any ideas why this seems to happen, why should the incorrect key be
>> selected just by changing the value to qualify on?
>>
>> I have tried a few more queries that are similar on other tables if
>> you would like them.
>
> I suspected something like this was happening, but your earlier
> EXPLAIN showed no indexes were being used at all, so I was a little
> stumped. It didn't seem to make sense otherwise.
>
> Try a USE INDEX and see if you can get a similar query plan on both
> queries.
>
> Baron
>
>

Ahhh... Thanks Baron!

This does indeed fix the problems :-)

mysql> explain select * from adverts use index (price) where price>0 and
section=1 order by price limit 0,20;
+----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+
| 1 | SIMPLE | table1 | range | price | price | 4 |
NULL | 128978 | Using where |
+----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from adverts use index (price) where price>1 and
section=1 order by price limit 0,20;
+----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+
| 1 | SIMPLE | table1 | range | price | price | 4 |
NULL | 128951 | Using where |
+----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+
1 row in set (0.00 sec)

select * from table1 use index (price) where price>1 and section=1 order
by price limit 0,20;
.........
20 rows in set (0.00 sec)

select * from table1 use index (price) where price>0 and section=1 order
by price limit 0,20;
.........
20 rows in set (0.00 sec)

This all looks good to me :-)

So, I'm just guessing the the query optimisation is going a little awry
on me. I'll have to watch for this in the future.

Thanks for all the guys (Baron, Dan, Jeremy & Michael), it's much
appreciated.

Chris.

11/09/2007, 18h14	#9
Baron Schwartz Messages: n/a Hébergeur:	Re: Really strange index/speed issues Chris Hemmings wrote: > Chris Hemmings wrote: >> Baron Schwartz wrote: >>> Hi Chris, >>> >>> Chris Hemmings wrote: >>>> Dan Buettner wrote: >>>>> Chris, a couple of thoughts - >>>>> >>>>> First, your index on the section is doing you no good (at this >>>>> time) since >>>>> all the values are the same. You may already know that, but >>>>> thought I'd >>>>> mention it. >>>>> >>>>> Second, my theory on why query #1 is faster - if all your prices >>>>> range from >>>>> 1 up, and you're querying for prices greater than 0, then MySQL can >>>>> just >>>>> return the first 30 rows after sorting them. >>>>> >>>>> The second query, where you are looking for prices greater than 1, >>>>> MySQL has >>>>> to sort and then examine a number of rows until it finds enough >>>>> matching >>>>> rows (price > 1) to satisfy your query. This likely takes a little >>>>> bit of >>>>> time. How many rows do you have with price = 1? It would have to >>>>> scan over >>>>> that many before it could start satisfying your query, if you think >>>>> about >>>>> it. >>>>> >>>>> HTH, >>>>> Dan >>>>> >>>>> >>>>> >>>>> On 9/10/07, Chris Hemmings <lists@cheeky.org> wrote: >>>>>> Hello, >>>>>> >>>>>> I have a table, currently holding 128,978 rows... In this table, >>>>>> I have a >>>>>> section column (int) and a price column (int). Every row has a >>>>>> section of >>>>>> 1 >>>>>> currently, every row has a price, ranging from 1 to 10,000. >>>>>> >>>>>> I have an index on both columns separately. >>>>>> >>>>>> Have a look at these two queries, can someone tell me why there is >>>>>> such a >>>>>> difference in speed of execution? (Note difference in price >>>>>> qualifier) >>>>>> >>>>>> ######################################## >>>>>> >>>>>> SELECT * >>>>>> FROM `table1` >>>>>> WHERE price >0 >>>>>> AND section =1 >>>>>> ORDER BY price >>>>>> LIMIT 0 , 30 >>>>>> >>>>>> Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) >>>>>> >>>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL >>>>>> 96734 >>>>>> Using >>>>>> where; Using filesort >>>>>> >>>>>> ######################################## >>>>>> >>>>>> SELECT * >>>>>> FROM `table1` >>>>>> WHERE price >1 >>>>>> AND section =1 >>>>>> ORDER BY price >>>>>> LIMIT 0 , 30 >>>>>> >>>>>> >>>>>> Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) >>>>>> >>>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL >>>>>> 96734 >>>>>> Using >>>>>> where; Using filesort >>>>>> >>>>>> ######################################## >>>>>> >>>>>> Other info: >>>>>> >>>>>> Query cacheing = off >>>>>> MySQL version = 5.0.32 >>>>>> OS = Debian Sarge >>>>>> >>>>>> Sure, the second query returns 29 fewer records than the first, >>>>>> but should >>>>>> that make the difference in time? >>>>>> >>>>>> Hope you can shed some light onto this :-) >>>>>> >>>>>> Ta! >>>>>> >>>>>> Chris. >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> MySQL General Mailing List >>>>>> For list archives: http://lists.mysql.com/mysql >>>>>> To unsubscribe: >>>>>> http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com >>>>>> >>>>>> >>>>> >>>> >>>> Thanks Dan, >>>> >>>> I've got you on the section index... I was going to use that later, >>>> when I get somre real data in there. >>>> >>>> Anyway, I agree with your logic, but, the inverse is happening. >>>> The one where it has to actually exclude some rows (because price>1) >>>> is actually faster. Thats really why this has me baffled, I would >>>> presume that the price>1 would be slower as it does have to filter >>>> rows out first. >>> >>> There's an easy way to find out: FLUSH STATUS, run the query, SHOW >>> STATUS LIKE 'handler%'. Do this on an otherwise quiet server if >>> possible.Or use MySQL Query Profiler -- it does a lot of math for you >>> :-)Baron >>> >>> >>> >>> >>> >>> >> >> Thanks Baron! >> >> I think you have hit upon something, doing what you said on a 'silent' >> server, I get the following: >> >> SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price >> LIMIT 0 , 30; >> >> 30 rows in set (0.00 sec) >> >> mysql> SHOW STATUS LIKE 'handler%'; >> +----------------------------+-------+ >> \| Variable_name \| Value \| >> +----------------------------+-------+ >> \| Handler_commit \| 0 \| >> \| Handler_delete \| 0 \| >> \| Handler_discover \| 0 \| >> \| Handler_prepare \| 0 \| >> \| Handler_read_first \| 0 \| >> \| Handler_read_key \| 1 \| >> \| Handler_read_next \| 29 \| >> \| Handler_read_prev \| 0 \| >> \| Handler_read_rnd \| 0 \| >> \| Handler_read_rnd_next \| 0 \| >> \| Handler_rollback \| 0 \| >> \| Handler_savepoint \| 0 \| >> \| Handler_savepoint_rollback \| 0 \| >> \| Handler_update \| 0 \| >> \| Handler_write \| 14 \| >> +----------------------------+-------+ >> 15 rows in set (0.00 sec) >> >> >> SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price >> LIMIT 0 , 30; >> >> 30 rows in set (0.95 sec) >> >> mysql> SHOW STATUS LIKE 'handler%'; >> +----------------------------+--------+ >> \| Variable_name \| Value \| >> +----------------------------+--------+ >> \| Handler_commit \| 0 \| >> \| Handler_delete \| 0 \| >> \| Handler_discover \| 0 \| >> \| Handler_prepare \| 0 \| >> \| Handler_read_first \| 0 \| >> \| Handler_read_key \| 1 \| >> \| Handler_read_next \| 128978 \| >> \| Handler_read_prev \| 0 \| >> \| Handler_read_rnd \| 30 \| >> \| Handler_read_rnd_next \| 0 \| >> \| Handler_rollback \| 0 \| >> \| Handler_savepoint \| 0 \| >> \| Handler_savepoint_rollback \| 0 \| >> \| Handler_update \| 0 \| >> \| Handler_write \| 14 \| >> +----------------------------+--------+ >> 15 rows in set (0.01 sec) >> >> So, the slower query obvisouly has the larger Handler_read_next number >> in it. Looking at the manual, it says the following for that variable: >> >> "The number of requests to read the next row in key order. This value >> is incremented if you are querying an index column with a range >> constraint or if you are doing an index scan." >> >> So, being a bit of a MySQL novice, I can't really see how >0 increases >> the number of reads by such a VAST amount... Can you guys put this in >> layman's terms for me? >> >> Thanks for the guys :-) >> >> Chris. >> > > Hi Guys, > > I'm still not having much luck with this, but, I have spotted something > that maybe I missed earlier. > > Doing an explain on both queries again produces the following: > > explain select * from table1 where price>0 and section=1 order by price > limit 0,20; > > \| 1 \| SIMPLE \| table1 \| range \| section,price \| section \| 4 \| NULL > \| 128966 \| Using where; Using filesort \| > > explain select * from table1 where price>1 and section=1 order by price > limit 0,20; > > \| 1 \| SIMPLE \| table1 \| range \| section,price \| price \| 4 \| > NULL \| 128951 \| Using where \| > > I'm not sure why these 'explains' looks different to the previous > example. For some reason, the second query (price>1) is using the > correct key, but on the first query, the wrong key is being used and > then a filesort has to take place. > > Any ideas why this seems to happen, why should the incorrect key be > selected just by changing the value to qualify on? > > I have tried a few more queries that are similar on other tables if you > would like them. I suspected something like this was happening, but your earlier EXPLAIN showed no indexes were being used at all, so I was a little stumped. It didn't seem to make sense otherwise. Try a USE INDEX and see if you can get a similar query plan on both queries. Baron

11/09/2007, 19h21	#10
Michael Dykman Messages: n/a Hébergeur:	Re: Really strange index/speed issues The results of an EXPLAIN have a lot to do with the data which is actually on the system. In this case, it seems to hinge on the distribution of your 'price' attribute.. how many records on your system? and what is the general distribution of the price attribute? (how many distinct values) On 9/11/07, Chris Hemmings <lists@cheeky.org> wrote: > Chris Hemmings wrote: > > Baron Schwartz wrote: > >> Hi Chris, > >> > >> Chris Hemmings wrote: > >>> Dan Buettner wrote: > >>>> Chris, a couple of thoughts - > >>>> > >>>> First, your index on the section is doing you no good (at this time) > >>>> since > >>>> all the values are the same. You may already know that, but thought > >>>> I'd > >>>> mention it. > >>>> > >>>> Second, my theory on why query #1 is faster - if all your prices > >>>> range from > >>>> 1 up, and you're querying for prices greater than 0, then MySQL can > >>>> just > >>>> return the first 30 rows after sorting them. > >>>> > >>>> The second query, where you are looking for prices greater than 1, > >>>> MySQL has > >>>> to sort and then examine a number of rows until it finds enough > >>>> matching > >>>> rows (price > 1) to satisfy your query. This likely takes a little > >>>> bit of > >>>> time. How many rows do you have with price = 1? It would have to > >>>> scan over > >>>> that many before it could start satisfying your query, if you think > >>>> about > >>>> it. > >>>> > >>>> HTH, > >>>> Dan > >>>> > >>>> > >>>> > >>>> On 9/10/07, Chris Hemmings <lists@cheeky.org> wrote: > >>>>> Hello, > >>>>> > >>>>> I have a table, currently holding 128,978 rows... In this table, I > >>>>> have a > >>>>> section column (int) and a price column (int). Every row has a > >>>>> section of > >>>>> 1 > >>>>> currently, every row has a price, ranging from 1 to 10,000. > >>>>> > >>>>> I have an index on both columns separately. > >>>>> > >>>>> Have a look at these two queries, can someone tell me why there is > >>>>> such a > >>>>> difference in speed of execution? (Note difference in price > >>>>> qualifier) > >>>>> > >>>>> ######################################## > >>>>> > >>>>> SELECT * > >>>>> FROM `table1` > >>>>> WHERE price >0 > >>>>> AND section =1 > >>>>> ORDER BY price > >>>>> LIMIT 0 , 30 > >>>>> > >>>>> Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) > >>>>> > >>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 > >>>>> Using > >>>>> where; Using filesort > >>>>> > >>>>> ######################################## > >>>>> > >>>>> SELECT * > >>>>> FROM `table1` > >>>>> WHERE price >1 > >>>>> AND section =1 > >>>>> ORDER BY price > >>>>> LIMIT 0 , 30 > >>>>> > >>>>> > >>>>> Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) > >>>>> > >>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 > >>>>> Using > >>>>> where; Using filesort > >>>>> > >>>>> ######################################## > >>>>> > >>>>> Other info: > >>>>> > >>>>> Query cacheing = off > >>>>> MySQL version = 5.0.32 > >>>>> OS = Debian Sarge > >>>>> > >>>>> Sure, the second query returns 29 fewer records than the first, but > >>>>> should > >>>>> that make the difference in time? > >>>>> > >>>>> Hope you can shed some light onto this :-) > >>>>> > >>>>> Ta! > >>>>> > >>>>> Chris. > >>>>> > >>>>> > >>>>> > >>>>> -- > >>>>> MySQL General Mailing List > >>>>> For list archives: http://lists.mysql.com/mysql > >>>>> To unsubscribe: > >>>>> http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com > >>>>> > >>>>> > >>>> > >>> > >>> Thanks Dan, > >>> > >>> I've got you on the section index... I was going to use that later, > >>> when I get somre real data in there. > >>> > >>> Anyway, I agree with your logic, but, the inverse is happening. The > >>> one where it has to actually exclude some rows (because price>1) is > >>> actually faster. Thats really why this has me baffled, I would > >>> presume that the price>1 would be slower as it does have to filter > >>> rows out first. > >> > >> There's an easy way to find out: FLUSH STATUS, run the query, SHOW > >> STATUS LIKE 'handler%'. Do this on an otherwise quiet server if > >> possible.Or use MySQL Query Profiler -- it does a lot of math for you > >> :-)Baron > >> > >> > >> > >> > >> > >> > > > > Thanks Baron! > > > > I think you have hit upon something, doing what you said on a 'silent' > > server, I get the following: > > > > SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price > > LIMIT 0 , 30; > > > > 30 rows in set (0.00 sec) > > > > mysql> SHOW STATUS LIKE 'handler%'; > > +----------------------------+-------+ > > \| Variable_name \| Value \| > > +----------------------------+-------+ > > \| Handler_commit \| 0 \| > > \| Handler_delete \| 0 \| > > \| Handler_discover \| 0 \| > > \| Handler_prepare \| 0 \| > > \| Handler_read_first \| 0 \| > > \| Handler_read_key \| 1 \| > > \| Handler_read_next \| 29 \| > > \| Handler_read_prev \| 0 \| > > \| Handler_read_rnd \| 0 \| > > \| Handler_read_rnd_next \| 0 \| > > \| Handler_rollback \| 0 \| > > \| Handler_savepoint \| 0 \| > > \| Handler_savepoint_rollback \| 0 \| > > \| Handler_update \| 0 \| > > \| Handler_write \| 14 \| > > +----------------------------+-------+ > > 15 rows in set (0.00 sec) > > > > > > SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price > > LIMIT 0 , 30; > > > > 30 rows in set (0.95 sec) > > > > mysql> SHOW STATUS LIKE 'handler%'; > > +----------------------------+--------+ > > \| Variable_name \| Value \| > > +----------------------------+--------+ > > \| Handler_commit \| 0 \| > > \| Handler_delete \| 0 \| > > \| Handler_discover \| 0 \| > > \| Handler_prepare \| 0 \| > > \| Handler_read_first \| 0 \| > > \| Handler_read_key \| 1 \| > > \| Handler_read_next \| 128978 \| > > \| Handler_read_prev \| 0 \| > > \| Handler_read_rnd \| 30 \| > > \| Handler_read_rnd_next \| 0 \| > > \| Handler_rollback \| 0 \| > > \| Handler_savepoint \| 0 \| > > \| Handler_savepoint_rollback \| 0 \| > > \| Handler_update \| 0 \| > > \| Handler_write \| 14 \| > > +----------------------------+--------+ > > 15 rows in set (0.01 sec) > > > > So, the slower query obvisouly has the larger Handler_read_next number > > in it. Looking at the manual, it says the following for that variable: > > > > "The number of requests to read the next row in key order. This value is > > incremented if you are querying an index column with a range constraint > > or if you are doing an index scan." > > > > So, being a bit of a MySQL novice, I can't really see how >0 increases > > the number of reads by such a VAST amount... Can you guys put this in > > layman's terms for me? > > > > Thanks for the guys :-) > > > > Chris. > > > > Hi Guys, > > I'm still not having much luck with this, but, I have spotted something > that maybe I missed earlier. > > Doing an explain on both queries again produces the following: > > explain select * from table1 where price>0 and section=1 order by price > limit 0,20; > > \| 1 \| SIMPLE \| table1 \| range \| section,price \| section \| 4 > \| NULL \| 128966 \| Using where; Using filesort \| > > explain select * from table1 where price>1 and section=1 order by price > limit 0,20; > > \| 1 \| SIMPLE \| table1 \| range \| section,price \| price \| 4 \| > NULL \| 128951 \| Using where \| > > I'm not sure why these 'explains' looks different to the previous > example. For some reason, the second query (price>1) is using the > correct key, but on the first query, the wrong key is being used and > then a filesort has to take place. > > Any ideas why this seems to happen, why should the incorrect key be > selected just by changing the value to qualify on? > > I have tried a few more queries that are similar on other tables if you > would like them. > > Thanks, > > Chris. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com > > -- - michael dykman - mdykman@gmail.com - All models are wrong. Some models are useful.

11/09/2007, 19h56	#11
Chris Hemmings Messages: n/a Hébergeur:	Re: Really strange index/speed issues Baron Schwartz wrote: > Chris Hemmings wrote: >> Chris Hemmings wrote: >>> Baron Schwartz wrote: >>>> Hi Chris, >>>> >>>> Chris Hemmings wrote: >>>>> Dan Buettner wrote: >>>>>> Chris, a couple of thoughts - >>>>>> >>>>>> First, your index on the section is doing you no good (at this >>>>>> time) since >>>>>> all the values are the same. You may already know that, but >>>>>> thought I'd >>>>>> mention it. >>>>>> >>>>>> Second, my theory on why query #1 is faster - if all your prices >>>>>> range from >>>>>> 1 up, and you're querying for prices greater than 0, then MySQL >>>>>> can just >>>>>> return the first 30 rows after sorting them. >>>>>> >>>>>> The second query, where you are looking for prices greater than >>>>>> 1, MySQL has >>>>>> to sort and then examine a number of rows until it finds enough >>>>>> matching >>>>>> rows (price > 1) to satisfy your query. This likely takes a >>>>>> little bit of >>>>>> time. How many rows do you have with price = 1? It would have >>>>>> to scan over >>>>>> that many before it could start satisfying your query, if you >>>>>> think about >>>>>> it. >>>>>> >>>>>> HTH, >>>>>> Dan >>>>>> >>>>>> >>>>>> >>>>>> On 9/10/07, Chris Hemmings <lists@cheeky.org> wrote: >>>>>>> Hello, >>>>>>> >>>>>>> I have a table, currently holding 128,978 rows... In this >>>>>>> table, I have a >>>>>>> section column (int) and a price column (int). Every row has a >>>>>>> section of >>>>>>> 1 >>>>>>> currently, every row has a price, ranging from 1 to 10,000. >>>>>>> >>>>>>> I have an index on both columns separately. >>>>>>> >>>>>>> Have a look at these two queries, can someone tell me why there >>>>>>> is such a >>>>>>> difference in speed of execution? (Note difference in price >>>>>>> qualifier) >>>>>>> >>>>>>> ######################################## >>>>>>> >>>>>>> SELECT * >>>>>>> FROM `table1` >>>>>>> WHERE price >0 >>>>>>> AND section =1 >>>>>>> ORDER BY price >>>>>>> LIMIT 0 , 30 >>>>>>> >>>>>>> Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) >>>>>>> >>>>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL >>>>>>> 96734 >>>>>>> Using >>>>>>> where; Using filesort >>>>>>> >>>>>>> ######################################## >>>>>>> >>>>>>> SELECT * >>>>>>> FROM `table1` >>>>>>> WHERE price >1 >>>>>>> AND section =1 >>>>>>> ORDER BY price >>>>>>> LIMIT 0 , 30 >>>>>>> >>>>>>> >>>>>>> Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) >>>>>>> >>>>>>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL >>>>>>> 96734 >>>>>>> Using >>>>>>> where; Using filesort >>>>>>> >>>>>>> ######################################## >>>>>>> >>>>>>> Other info: >>>>>>> >>>>>>> Query cacheing = off >>>>>>> MySQL version = 5.0.32 >>>>>>> OS = Debian Sarge >>>>>>> >>>>>>> Sure, the second query returns 29 fewer records than the first, >>>>>>> but should >>>>>>> that make the difference in time? >>>>>>> >>>>>>> Hope you can shed some light onto this :-) >>>>>>> >>>>>>> Ta! >>>>>>> >>>>>>> Chris. >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> MySQL General Mailing List >>>>>>> For list archives: http://lists.mysql.com/mysql >>>>>>> To unsubscribe: >>>>>>> http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> Thanks Dan, >>>>> >>>>> I've got you on the section index... I was going to use that >>>>> later, when I get somre real data in there. >>>>> >>>>> Anyway, I agree with your logic, but, the inverse is happening. >>>>> The one where it has to actually exclude some rows (because >>>>> price>1) is actually faster. Thats really why this has me >>>>> baffled, I would presume that the price>1 would be slower as it >>>>> does have to filter rows out first. >>>> >>>> There's an easy way to find out: FLUSH STATUS, run the query, SHOW >>>> STATUS LIKE 'handler%'. Do this on an otherwise quiet server if >>>> possible.Or use MySQL Query Profiler -- it does a lot of math for >>>> you :-)Baron >>>> >>>> >>>> >>>> >>>> >>>> >>> >>> Thanks Baron! >>> >>> I think you have hit upon something, doing what you said on a >>> 'silent' server, I get the following: >>> >>> SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price >>> LIMIT 0 , 30; >>> >>> 30 rows in set (0.00 sec) >>> >>> mysql> SHOW STATUS LIKE 'handler%'; >>> +----------------------------+-------+ >>> \| Variable_name \| Value \| >>> +----------------------------+-------+ >>> \| Handler_commit \| 0 \| >>> \| Handler_delete \| 0 \| >>> \| Handler_discover \| 0 \| >>> \| Handler_prepare \| 0 \| >>> \| Handler_read_first \| 0 \| >>> \| Handler_read_key \| 1 \| >>> \| Handler_read_next \| 29 \| >>> \| Handler_read_prev \| 0 \| >>> \| Handler_read_rnd \| 0 \| >>> \| Handler_read_rnd_next \| 0 \| >>> \| Handler_rollback \| 0 \| >>> \| Handler_savepoint \| 0 \| >>> \| Handler_savepoint_rollback \| 0 \| >>> \| Handler_update \| 0 \| >>> \| Handler_write \| 14 \| >>> +----------------------------+-------+ >>> 15 rows in set (0.00 sec) >>> >>> >>> SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price >>> LIMIT 0 , 30; >>> >>> 30 rows in set (0.95 sec) >>> >>> mysql> SHOW STATUS LIKE 'handler%'; >>> +----------------------------+--------+ >>> \| Variable_name \| Value \| >>> +----------------------------+--------+ >>> \| Handler_commit \| 0 \| >>> \| Handler_delete \| 0 \| >>> \| Handler_discover \| 0 \| >>> \| Handler_prepare \| 0 \| >>> \| Handler_read_first \| 0 \| >>> \| Handler_read_key \| 1 \| >>> \| Handler_read_next \| 128978 \| >>> \| Handler_read_prev \| 0 \| >>> \| Handler_read_rnd \| 30 \| >>> \| Handler_read_rnd_next \| 0 \| >>> \| Handler_rollback \| 0 \| >>> \| Handler_savepoint \| 0 \| >>> \| Handler_savepoint_rollback \| 0 \| >>> \| Handler_update \| 0 \| >>> \| Handler_write \| 14 \| >>> +----------------------------+--------+ >>> 15 rows in set (0.01 sec) >>> >>> So, the slower query obvisouly has the larger Handler_read_next >>> number in it. Looking at the manual, it says the following for that >>> variable: >>> >>> "The number of requests to read the next row in key order. This >>> value is incremented if you are querying an index column with a >>> range constraint or if you are doing an index scan." >>> >>> So, being a bit of a MySQL novice, I can't really see how >0 >>> increases the number of reads by such a VAST amount... Can you guys >>> put this in layman's terms for me? >>> >>> Thanks for the guys :-) >>> >>> Chris. >>> >> >> Hi Guys, >> >> I'm still not having much luck with this, but, I have spotted >> something that maybe I missed earlier. >> >> Doing an explain on both queries again produces the following: >> >> explain select * from table1 where price>0 and section=1 order by >> price limit 0,20; >> >> \| 1 \| SIMPLE \| table1 \| range \| section,price \| section \| 4 \| >> NULL \| 128966 \| Using where; Using filesort \| >> >> explain select * from table1 where price>1 and section=1 order by >> price limit 0,20; >> >> \| 1 \| SIMPLE \| table1 \| range \| section,price \| price \| 4 >> \| NULL \| 128951 \| Using where \| >> >> I'm not sure why these 'explains' looks different to the previous >> example. For some reason, the second query (price>1) is using the >> correct key, but on the first query, the wrong key is being used and >> then a filesort has to take place. >> >> Any ideas why this seems to happen, why should the incorrect key be >> selected just by changing the value to qualify on? >> >> I have tried a few more queries that are similar on other tables if >> you would like them. > > I suspected something like this was happening, but your earlier > EXPLAIN showed no indexes were being used at all, so I was a little > stumped. It didn't seem to make sense otherwise. > > Try a USE INDEX and see if you can get a similar query plan on both > queries. > > Baron > > Ahhh... Thanks Baron! This does indeed fix the problems :-) mysql> explain select * from adverts use index (price) where price>0 and section=1 order by price limit 0,20; +----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+ \| 1 \| SIMPLE \| table1 \| range \| price \| price \| 4 \| NULL \| 128978 \| Using where \| +----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from adverts use index (price) where price>1 and section=1 order by price limit 0,20; +----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+ \| 1 \| SIMPLE \| table1 \| range \| price \| price \| 4 \| NULL \| 128951 \| Using where \| +----+-------------+---------+-------+---------------+-------+---------+------+--------+-------------+ 1 row in set (0.00 sec) select * from table1 use index (price) where price>1 and section=1 order by price limit 0,20; ......... 20 rows in set (0.00 sec) select * from table1 use index (price) where price>0 and section=1 order by price limit 0,20; ......... 20 rows in set (0.00 sec) This all looks good to me :-) So, I'm just guessing the the query optimisation is going a little awry on me. I'll have to watch for this in the future. Thanks for all the guys (Baron, Dan, Jeremy & Michael), it's much appreciated. Chris.

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email