Solved

Mysql join performance?

Posted on 2009-05-11
12
450 Views
Last Modified: 2012-05-06
i have two tables

Table 1 users it have 100 records
user_id,username

Table 2 payments have 9000 records
payment_id,user_id

i have two query which is the best

Query1
select users.username from users,payments where users.user_id=payments.user_id

Query2
select users.username from users,payments where payments.user_id=users.user_id

0
Comment
Question by:phparmy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 100 total points
ID: 24352666
You can check from your execution plan if you have SQL Server but on the fly I can say first would give you better performance.
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 24352715
On MySQL, try the EXPLAIN SELECT command for each of your queries - it will give you the details of the indexes used and some additional info. Post that info here if you need more help :)
EXPLAIN select users.username from users,payments where users.user_id=payments.user_id;
EXPLAIN select users.username from users,payments where payments.user_id=users.user_id;

Open in new window

0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 24352735
By the way, there should not be any difference for the two queries you mentioned. You might see some difference if you try these:
EXPLAIN select users.username from users INNER JOIN payments ON users.user_id=payments.user_id;
EXPLAIN select users.username from users LEFT JOIN payments ON users.user_id=payments.user_id;

Open in new window

0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24352752
leannonn,

use of LEFT join is not needed I guess, Author only wants to know the differences, I guess he don't want to change the logic as LEFT JOIN may come up with some unwanted data.
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 24352774
RiteshShah, I understand that - however the queries he specified are identical as far as the DB is concerned. I just offered some variation he might consider.
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 24352789
I correct myself and here are two queries that will give identical results, but may have different EXPLAIN info:
EXPLAIN select users.username from users INNER JOIN payments ON users.user_id=payments.user_id;
EXPLAIN select users.username from users LEFT JOIN payments ON users.user_id=payments.user_id WHERE payments.user_id IS NOT NULL;

Open in new window

0
 
LVL 5

Accepted Solution

by:
TheVeee earned 200 total points
ID: 24353527
Query 2 will be the fastest for performance.  First Im hoping you have a index on each of the fields for both databases on the userid field.  This will cut down on the table space scans.  Second you always want to hit against the least amount records, this case in your example Table 1 (100 Records).

Basically how the query will work is first get the list of records via the index field in table 1, then take that index and drive specifically to the record index on table 2  for 100 times.

If you would have done query1 you would have checked each record in table 2 index list against table 1 record index list for 9000 times.

Again rule of thumb, ALWAYS pick the smallest result set first to eliminate all unneccessary fields.
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 200 total points
ID: 24354258
This will vary between database. You asked this in 3 zones, so...

Oracle:
When using cost-base optimizer these queries are identical, as Oracle will know how many rows per table and use the correct driving table.
When using older rules based optimizer (pre-10g), or using hints, the 1st query may run faster because you want the driving table to be the one with the smallest record count. Since both of your FROM clauses are identical, the only difference is in your WHERE clause, and Oracle will want the driving tables from LEFT to RIGHT in the WHERE clause.

In the other databases, I cannot say for certain, but the general rules apply.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24374265
C grade? I will think twice before offering my opinion for your questions, thank you very much. You did not even respond to any of the suggestions, in form of discussion, then graded with a C?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374274
mrjoltcola,

I was about the ask the same, why "C" grade?
0
 
LVL 5

Expert Comment

by:TheVeee
ID: 24376046
phpArmy, next time if your going to give a C without giving ANY feed back during the whole process, dont bother even assigning the points.  See if you had been the phpMARINES... you would have done right... oh well, I have you on my list so you wont see my assistance any more, have a great day!
0
 

Author Comment

by:phparmy
ID: 24377884
sorry for C grade its my first C grade i give you points but dont know that C grade is bad because it is explained as Average. Fistly thanks for answers but looking for answers one is says first query other says second query i dont ask poll question i only want to understand logic iam searching for 3 three days MSSQL,MySQL and Oracle books,web sites but not found any thing about that i also asked to mysqlperformanceblog.com.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL - Aging Report - Display Months with no data 8 43
Do not display comma when no last name 8 48
Mysql query one to many 11 37
t-sql left join 2 31
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question