4

I'm using MySQL Database and i have two tables. They are User and Reservation

Here are my question.

  1. Currently, I used LEFT JOIN what about SubQuery with NOT EXIST. which is better in performance point of view?
  2. Can i create views for this query, Can that make any difference in performance

User

| FIELD |        TYPE | NULL | KEY | DEFAULT |          EXTRA |
|-------|-------------|------|-----|---------|----------------|
|   uid |     int(11) |   NO | PRI |  (null) | auto_increment |
| uname | varchar(30) |  YES |     |  (null) |                |

Reservation

|    FIELD |      TYPE | NULL | KEY |           DEFAULT |          EXTRA |
|----------|-----------|------|-----|-------------------|----------------|
|      rid |   int(11) |   NO | PRI |            (null) | auto_increment |
|      uid |   int(11) |  YES | MUL |            (null) |                |
| reserved | timestamp |   NO |     | CURRENT_TIMESTAMP |                |

SQL Code:

create table user (
 uid int not null auto_increment,
 uname varchar(30),
 primary key(uid)
);

create table reservation (
 rid int not null auto_increment,
 uid int,
 reserved timestamp not null default CURRENT_TIMESTAMP,
 primary key(rid),
 foreign key (uid) references user (uid)
)

My Current Working SQL Query

SELECT u.uid, u.uname, date_format(reserved, '%Y%m')  
FROM user as u 
LEFT JOIN reservation as r on  
r.uid = u.uid and date_format(reserved, '%Y%m') = 201307  
where r.uid is null
Venkat.R
  • 7,420
  • 5
  • 42
  • 63

4 Answers4

2

Here's an excellent article about performance differences: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

Summary:

...the best way to search for missing values in MySQL is using a LEFT JOIN / IS NULL or NOT IN rather than NOT EXISTS.

But you can improve performance a little by putting an index on reserved column and rewriting your query like this:

reserved >= '2013-07-01 00:00:00' AND reserved < '2013-08-01 00:00:00'

Views don't change anything of the performance of the query.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
1

The problem is, if you do a date_format the MySQL index is not used. You should use something like this:

reserved >= '2013-07-01 00:00:00' AND reserved < '2013-08-01 00:00:00'

Than the index is used and your query will be faster. if you have a combined index on the table reservation, with the fields, uid, reserved

Niels
  • 48,601
  • 4
  • 62
  • 81
0

This would be better with not exists.

SELECT u.uid, u.uname, date_format(reserved, '%Y%m')  
FROM user as u where not exist (select 1 from reservation as r   
where r.uid = u.uid and date_format(reserved, '%Y%m') = 201307)

View doesnt help much in performance. It mostly concerns about reusability, data hiding and security reasons.

DB_learner
  • 1,026
  • 9
  • 15
  • This does not replies to OP's question (i.e. what is better and why) – Alma Do Aug 28 '13 at 09:42
  • I have specified "not exist" performs better than left join in this scenario. And to answer why, not exist (semi join) just checks for existence of data in the sub query. Where as left join had to join the two tables followed by a filter predicate – DB_learner Aug 28 '13 at 09:45
  • Left Join is better than Sub query! Check this link: http://stackoverflow.com/questions/2577174/join-vs-subquery – Venkat.R Aug 28 '13 at 10:02
  • Not always, try to understand the scenario here, he is trying to do (A minus b) with help of left join. Dont go blindly with join vs subquery. It depends on what scenario we are in and what we want. – DB_learner Aug 28 '13 at 10:07
0

You'll want to use NOT EXISTS, and making a view won't really do much for you, even convenience wise since this is a pretty simple query.

Also see: SQL performance on LEFT OUTER JOIN vs NOT EXISTS

Community
  • 1
  • 1
chucknelson
  • 2,328
  • 3
  • 24
  • 31