MySQL Forums
Forum List  »  Optimizer & Parser

Re: what does Collapse condition mean?
Posted by: Jørgen Løland
Date: April 11, 2011 12:02AM

Hi Jongse,

In this context, "collapse" means that (key1 LIKE 'abcde%' OR TRUE) is always TRUE, so this part of the condition is replaced with TRUE. (key1 < 'uux' AND key1 > 'z') is always FALSE, so this part is replaced with FALSE.

Note that this part of the documentation describes how MySQL builds range conditions that can be used when reading ranges from a certain index, in this case an index over columns "key1". It does not mean that parts of the WHERE condition is not evaluated.

When doing this, MySQL starts with the entire WHERE condition and then removes
1) the parts that are irrelevant to the index in question
2) the parts that are such that if evaluated to FALSE for the columns in this index does not necessarily disqualify the row from being in the result set.

After this, the remaining condition that may be used for range access will include all rows qualifying for the result set and as few other rows as possible. Again, these "removed" parts of the WHERE condition are still evaluated, just not as part of the range access. Consider the example on the page you are referring [1]:

Original query:
SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

Extracted key for key1: (key1 < 'bar')

Content of table t1 (my example data):
key1 nonkey
-----------
aaa  1       ---> Fetched by range access on key1
aab  2       ---> Fetched by range access on key1
abc  1       ---> Fetched by range access on key1
...
baq  3       ---> Fetched by range access on key1
bar  4       X
bas  5       X
...
uux  8       X
uuz  9       X

The full condition is then evaluated on the fetched rows to see which should be in the result set.

[1] http://dev.mysql.com/doc/refman/5.5/en/range-access-single-part.html

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com



Edited 1 time(s). Last edit at 04/11/2011 12:04AM by Jorgen Loland.

Options: ReplyQuote


Subject
Views
Written By
Posted
3926
April 10, 2011 07:25PM
Re: what does Collapse condition mean?
3000
April 11, 2011 12:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.