3

I have this sql query. I now what is it returning , but can't understand how server parses and execute it step by step. I want to understand the principles of work in deep level, what is going on server when you run sql command, how it iterates in each row before return result.

enter image description here

I have users and friends table, in last one have friend_id and user_id combination, both of them are ids of users.

select u.id,f.user_id, f.friend_id from friends f
    INNER JOIN users u on (u.id = f.friend_id or u.id = f.user_id)
    where f.user_id = 72 or f.friend_id = 72
  • How can explain me what is going on in each step of this query?
  • How it is behaves in each step?
  • How OR operator is behaves in each step here?
  • Does it check both statements of OR operator in each step or query, or e.g. in first iteration joins with friend_id then with user_id.
Anna Gabrielyan
  • 2,120
  • 3
  • 28
  • 48
  • 1
    It would be worth removing the incorrect tag. Is this MySQL or SQL Server? – Rich Benner Nov 30 '16 at 15:36
  • Does MySQL have an execution plan similar to SQL Server? – SS_DBA Nov 30 '16 at 15:40
  • I have used EXPLAIN command, but it is not what I am looking for, I need to understand the principles of working select query.How is it works, or compiling. – Anna Gabrielyan Nov 30 '16 at 15:42
  • It;s worth googling mysql order of execution. You might find something like this http://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order – P.Salmon Nov 30 '16 at 15:46
  • I think I have used wrong title, I don't need the query statement order. I want to understand the principles of work in deep level, what is going on server when u run sql command, how it iterates in each row. – Anna Gabrielyan Nov 30 '16 at 15:50
  • Anulk, sql commands run in sets they are not iterating by row, – HLGEM Dec 01 '16 at 21:09

1 Answers1

2

Without a query plan I can only make a guess how the engine could potentially execute your query, I hope it helps:

First step - FROM:

If you have an index on the where columns (user_id, user_id), an index seek can be executed on your friends table with the where conditions to limit the results which need to be evaluated later in the execution plan. If you don't have an index, a table scan can be performed.

Your query would select all rows from the friends table where user_id or friend_id is equal to 72.

OR means here that only one condition needs to be true for the row to be added to the data stream of rows from friends.

2nd step - JOIN:

After the friends rows are found applying the where predicate, the actual join processing would start. Depending on the table statistics and query costs the engine could do a nested loop join where each row is evaluated from friends against users. It could also build a hash table for one table and probe each row from the other table using the hash key function. This way the ids can be matched between your two tables (friends and users)

The ON defines which columns it needs to compare.

Let's consider the case of a nested loop join which is easier to understand. This join type loops through both data streams ( friends and users ) which are being joined together in a row by row fashion and evaluates each row.

In your query it will compare the u.id against friend_id. If this condition returns true the join will be fulfilled and the engine will combine the matching row from table friends and users for further processing. If the first condition of ON is false or unknown the engine will evaluate the second condition user_id. True fulfills the join, false or unknown means the row is not matched so it will not be returned as in this case both on conditions would fail.

3rd step - SELECT:

After the tables are joined the engine has all the data it needs to finally execute the SELECT statement returning the columns you've asked it for.

Mathias Florin
  • 48
  • 3
  • 19