In MySQL, get a user who is not reserved for the last month

Question

I am using MySQL Database and I have two tables. They are User and Reservation

Here is my question.

• Currently, I used LEFT JOIN how about SubQuery with NOT EXIST . which is better in terms of performance?
• Can I create views for this query, can it 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 

Answer 1

Here's a great article on performance differences: NOT IN vs. NOT EXISTS vs LEFT JOIN / IS NULL: MySQL

Summary:

... the best way to find missing values ​​in MySQL is to use LEFT JOIN / IS NULL or NOT IN, not EXISTS.

But you can improve performance a bit by placing the index on a reserved column and rewriting your query as follows:

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

Views do not change anything from query performance.

Answer 2

The problem is that if you use 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' 

What index is used, and your request will be faster. if you have a combined index in a table reservation, with uid, reserved fields

Answer 3

It would be better if it weren’t.

 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) 

Viewing does not help in performance. This mainly relates to reuse, data hiding and security considerations.

Answer 4

You want to use NOT EXISTS , and creating a view is actually not enough for you, even convenient, because it is a fairly simple query.

See also: SQL performance in LEFT OUTER JOIN vs. NOT EXISTS