INNER JOIN execution/evaluation order

Question

I was wondering how exactly inner joins works in mysql.

If I do

SELECT * FROM A a 
INNER JOIN B b ON a.row = b.row
INNER JOIN C c ON c.row2 = b.row2
WHERE name='Paul';

Does it do the joins first, then pick the ones where name = paul? Because when I do it this way, it is SUPER DUPER slow.

is there a way to do something along the lines:

SELECT * FROM (A a WHERE name='paul')
INNER JOIN B b ON a.row = b.row
INNER JOIN C c ON c.row2 = b.row2]

When I try it that way, I just get an error.

or alternately, is it better to just have 3 separate queries, one for A, B and C? example:

string query1 = "SELECT * FROM A WHERE name = 'paul'";
//send query, get data reader
string query2 = "SELECT * FROM b WHERE b = " + query1.b;
//send query, get data reader
string query3 = "SELECT * FROM C WHERE c = " + query1.c;
//send query, get data reader

Obviously this is just pseudo code, but I think it illustrates the point.

Which way is faster/recommended?

Edit Table structure:

**tblTimesheet**
int timesheetID (primary key)
datetime date
varchar username
int projectID
string description
float hours

**tblProjects**
int projectID (primary key)
string project name
int clientID

**tblClients**
int clientID
string clientName

The join that I want is:

select * from tblTimesheet time
INNER JOIN tblProject proj on time.projectID = proj.projectID
INNER JOIN tblClient client on proj.clientID = client.clientID
WHERE username = 'paul';

something like that

This subject is also touched upon in this question - http://stackoverflow.com/questions/228424/in-what-order-are-mysql-joins-evaluated — dash, Jun 29 '12 at 16:16

score 2 · Answer 1 · answered Jun 29 '12 at 16:09

2

It's fantastically unlikely a join will be slower than three database hits. Reordering the clauses shouldn't have an impact either if MySQL's query optimizer is at all competent. Are the columns in the WHERE / ON clauses indexed?

answered Jun 29 '12 at 16:09

millimoose

39,073
9
82
134

Ya, I didnt think that way would be faster...Just thought I'd ask. what do you mean by indexed? – Toadums Jun 29 '12 at 16:11
@Toadums If a column is indexed, it means you want to spend some disk space / memory and CPU time when inserting a value into it to make retrievals much much faster. You should add an index to every column that you're going to use to look up rows. I also recommend paging through an introductory database systems textbook. – millimoose Jun 29 '12 at 17:42

score 2 · Answer 2 · answered Jun 29 '12 at 16:10

2

I think you'll find the query optimiser will give you the best possible query most of the time. You need to look at the execution plan to find out why the query is slow - my guess is lack of indexes.

When MySql looks in these tables, it will usually do it in the best way to get the best speed - a simple join as you've illustrated won't confuse the query optimiser, but missing indexes can cause the database engine to scan tables instead of looking up values (i.e. it needs to walk through the table row by row to match the criteria you specified)

An index ensures that the engine doesn't need to go searching down to the leaf page level and will usually speed up queries

What's the table structure here or is this all hypothetical?

The general rule of thumb with SQL is - try it and see!

answered Jun 29 '12 at 16:10

Charleh

13,749
3
37
57

I updated my question, showing the table structure. Everyone has said to use indexes, how would I do that with my example? thanks! – Toadums Jun 29 '12 at 16:19
1

ALTER TABLE TableName ADD INDEX IndexName (FieldName) – Jeff Jun 29 '12 at 16:32
@Jeff Good, seems easy! So would I want an index on Username then? (I assume a primary key is even faster than an index?) – Toadums Jun 29 '12 at 16:42
@Toadums You should take a look at this tutrial to help you: http://www.tizag.com/mysqlTutorial/mysql-index.php. You can create your index this way, too: CREATE INDEX timesheet_username_index ON tblTimesheet(username) – dash Jun 29 '12 at 16:46
@dash ya, I have gone through that! I understand what they do, I just wanted to make sure I wasn't 'over-indexing' :) – Toadums Jun 29 '12 at 16:49
@Toadums - Once you do more of this, you'll begin to get a feel for when you are over-indexing. Don't worry about it at this stage. You might also want to have a quick look at the resources I link to in my answer as they are good reference material - EXPLAIN in particular is a vital tool in understanding query performance. Have fun! – dash Jun 29 '12 at 16:51
@Toadums The overhead of using too many indices is likely to be negligible compared to the performance hit of an unindexed lookup. (Which is *exponentially* slower than an indexed one.) Especially given that in most databases, reads dominate writes. – millimoose Jun 29 '12 at 17:47

score 2 · Accepted Answer · answered Jun 29 '12 at 16:28

You are probably missing an index on a key table; you can use the MySql EXPLAIN keyword to help in finding out where your query is slow.

To answer another section of your question;

is there a way to do something along the lines:

SELECT * FROM (A a WHERE name='paul')
INNER JOIN B b ON a.row = b.row
INNER JOIN C c ON c.row2 = b.row2]

You can use a SubQuery;

SELECT * 
FROM (SELECT * FROM  tblTimesheet WHERE username = 'Paul') AS time
INNER JOIN tblProject proj on time.projectID = proj.projectID
INNER JOIN tblClient client on proj.clientID = client.clientID

What this query is effectively doing is attempting to prefilter the fields the JOIN will operate on. Rather than join all the fields to together, and then filter those down, it only attempts to JOIN fields from tblTimesheet where the name is 'Paul' first.

However, the query optimizer should already be doing this so this query should perform similarly to your original query.

For more help with indexes, the understanding of which will aid you greatly in database development, start by looking at a tutorial like this one.

score 1 · Answer 4 · answered Jun 29 '12 at 16:10

1

Use your first query, the mySql query optimizer should pick the fastest strategy

if you want it to be faster, make sure that there is an index on the name column

answered Jun 29 '12 at 16:10

paul

21,653
1
53
54

INNER JOIN execution/evaluation order

4 Answers4