Search code examples
sqlrdbms

sql - retrieving order

I was explaining my SQL query to a colleague yesterday and he asked me a question I never considered.

Let's say I have a table called the_table like this 

col1            col2
www.toto.com    stuff_wedding
www.toto.com    stuff_boxing
www.toto.com    stuff_love
www.toto.com    stuff_wedding

My query is :

SELECT * FROM the_table WHERE col1 LIKE '%toto.com%' AND col LIKE '%wedding%';

I should get 

col1            col2
www.toto.com    stuff_wedding
www.toto.com    stuff_wedding

The question is the following:

When doing the AND like in my condition, does the SQL parsing go for every line ,looking for toto on col1 and once the query found them , regroup all the results with toto in col1 and look look for wedding in col2 in that specific subset?

Or does it look at any line , look for toto for col1 and then go for col2 if there was a toto in col1 , and look for wedding?

I was a bit puzzled on that one as I've never really thought about it. Plus , does the DB engine has something to do with how the parsing is done?

Thanks

Solution

  • For SQL server check this Understanding-how-SQL-Server-executes-a-query

    I think your answer is here:

    Seek Operator
    The seek operator can locate a row directly based on a key. Seek can only operate on B-Tree organized data sources, so it can only be applied to Clustered and Nonclustered indexes. If an index has a complex key (multiple columns) then the Seek operator can only operate if values for the leftmost keys in the index definition are provided. To give an example, if an index has the key columns (A, B, C) then the Seek can locate the first row where A='a', or the first row where A='a' AND B='b' or the first row where A='a' AND B='b' AND C='c'. However, on such an index, Seek cannot locate a row where B='b' or a row where C='c'. Seek operator is also capable of implementing ranges. Given the same index definition on (A, B, C) a Seek operator can iterate all rows where A > 'a' AND A < 'z' or all rows where A = 'a' AND B > 'b' AND B < 'z', but it cannot iterate rows where B > 'b' AND B < 'z'. If you inspect an execution plan you will possibly see any of the operators Clustered Index Seek, or Remote Index Seek. They are distinct operators because they apply to different data sources, but they all have in common the capability to efficiently locate locate a row base dona key value or to iterate efficiently over a range of key values. Obviously there are no heap seek operators, as heaps, being unordered, do not have the capability to locate a row efficiently based on a key. Seek should be be the preferred data access method in almost every situation.