Loose index scan

[Andy]

Hi,

I was looking for information on MySQL's use of index in queries and
stumbled upon this blog post of Peter's:

http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-and-loose-index-scan/

A passage that particularly got my attention is:

"if I would have (A>0 and B>6) clause and index (A,B) I expected it
would start looking at all values where A>0 instantly jumping to onces
have B>6 by using index. It is possibe. So I was shocked and upset to
find out it did not."

I have a (somewhat) similar situation. I have a table:

CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT,
other_id INT NOT NULL,
expiration_datetime DATETIME,
score INT,
PRIMARY KEY (id),
INDEX order_by_index (other_id, expiration_datetime, score)
)

I need to run query in the form of:

SELECT * FROM mytable
WHERE other_id=1 AND expiration_datetime > NOW()
ORDER BY score LIMIT 10

Would the above query be able to use order_by_index? At first I was
sure it should be. But after reading Peter's blog post I'm not so sure
anymore.

The blog post is 4 years old so the situation might have changed.
Could anyone clarify please?

Thanks

[Baron Schwartz]

Hi Andy,

The situation is the same -- MySQL still cannot do a loose index scan
(or index-skip-scan if that's more familiar terminology). Internally,
I'm pretty sure InnoDB can actually do that, but it isn't supported in
the storage engine API.

Baron

--
Chief Performance Architect at Percona <http://www.percona.com/>
+1 (888) 401-3401 x507
Calendar: <http://bit.ly/baron-percona-cal> (Eastern Time)
Flat-rate 24x7 support for MySQL <http://tinyurl.com/2fcmn34>

[Andy]

Hi Baron,

Thanks for the response. What I'm wondering is if loose index scan is
even needed in my query. It seems to me that the query itself is
perfectly mapped to the index and it *shouldn't* require anything more
than a simple index lookup. But the query optimizer seems very magical
so I'm not sure. Can you shed some light on this? Thanks.

Andy

On Nov 3, 7:44 am, Baron Schwartz <ba...@percona.com> wrote:
> Hi Andy,
>
> The situation is the same -- MySQL still cannot do a loose index scan
> (or index-skip-scan if that's more familiar terminology).  Internally,
> I'm pretty sure InnoDB can actually do that, but it isn't supported in
> the storage engine API.
>
> Baron
>
> On Tue, Nov  2 at 19:04 Andy wrote:
>
>
>
>
>
>
>
>
>
> > Hi,
>
> > I was looking for information on MySQL's use of index in queries and
> > stumbled upon this blog post of Peter's:
>

> >http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-an...

[Peter Zaitsev]

Baron,

BTW there is an exception which is very rare. MySQL would do something
similar to skip-scan in case

INDEX(A,B)

SELECT MAX(B) FROM T GROUP BY A;

It however would NOT be able to do general skip scan such as

SELECT * FROM T WHERE B=5   even if  A just has couple of values and it would be very good to do.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

--
Peter Zaitsev, CEO, Percona Inc.
Tel: +1 888 401 3401 ext 501   Skype:  peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

Percona Training Workshops
http://www.percona.com/training/

[Jim Lyons]

According to the "High Performance MySQL" book of Baron, Peter, et. al. if you have a composite index it will use the left-most columns if they appear in the 'where' clause up to and including the first one that uses a range, but none after that. 

So in your example with the index on (other_id, expiration_datetime, score), the query

SELECT * FROM mytable
WHERE other_id=1 AND expiration_datetime > NOW()
ORDER BY score LIMIT 10

would use other_id AND expiration_datetime.  If the 'where' clause were:

WHERE other_id=1 AND expiration_datetime > NOW() and score = 80

it would STILL only use other_id and expiration_datetime.  It could not use score because of the range search for expiration_datetime.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com