CODEWITHSUNDEEP
mysqlsql
user3138541
user3138541

Reputation: 29

Simple mysql join for specific date

I have two tables friends and birthdays.

birthdays schema: userid and birthday_date

friends schema: userid and userid2.

How can I find out the friends of the people who have a birthday day today? I am thinking something like this:

SELECT *
FROM friends
WHERE userid2 IN
    (SELECT userid
     FROM birthdays
     WHERE birthday_date='05/02')

Is this the best way to do it?

Here is the EXPLAIN response that I get:

+----+--------------------+-----------------------------+-----------------+-----------------------+---------+---------+------+-----------+--------------------------+
| id | select_type        | table                       | type            | possible_keys         | key     | key_len | ref  | rows      | Extra                    |
+----+--------------------+-----------------------------+-----------------+-----------------------+---------+---------+------+-----------+--------------------------+
| 1  | PRIMARY            | friends          | index           | NULL                  | userid2  | 8       | NULL | 4192662 | Using where; Using index |
| 2  | DEPENDENT SUBQUERY | birthdays | unique_subquery | PRIMARY,birthday_date | PRIMARY | 8       | func | 1         | Using where              |
+----+--------------------+-----------------------------+-----------------+-----------------------+---------+---------+------+-----------+--------------------------+

It doesn't seem optimized if it is searching 4 million rows.

Also, there are indexes on every column.

Upvotes: 0

Views: 62

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

TO make the query faster you need proper index on both tables in conjunction with the JOIN.

Here is an example

create table birthdays (userid int,birthday_date varchar(20));
insert into birthdays values (1,'1980-01-01');
insert into birthdays values(2,'1980-01-02');
insert into birthdays values(3,'1980-01-03');
insert into birthdays values(4,'1980-01-04');
insert into birthdays values(5,'1980-01-05');
insert into birthdays values(6,'1980-01-06');
insert into birthdays values(7,'1980-01-07');

create table friends(userid int, userid2 int);
insert into friends values(3,1);
insert into friends values(1,3);
insert into friends values(1,5);
insert into friends values(2,3);
insert into friends values (7,6);

alter table birthdays add index bday_idx (`birthday_date`);
alter table birthdays add index iduser_idx (`userid`);

alter table friends add index userid2_idx(userid2)

Now if I run the following query it will be for sure way faster 

SELECT f.*
FROM friends f 
inner join birthdays b on b.userid = f.userid2 
WHERE b.birthday_date='1980-01-01'

ID  SELECT_TYPE     TABLE   TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    EXTRA
1   SIMPLE  b   ref     bday_idx,iduser_idx     bday_idx    63  const   1   Using where
1   SIMPLE  f   ref     userid2_idx     userid2_idx     5   db_2_99839.b.userid     1   Using where

DEMO

NOTE : When you join 2 tables with a common key make sure both of them are of same datatype and length, in the above example its joining userid (birthday) and userid2(friends) so they are of same data type and length. Where condition column needs to be indexed for faster fetch and the optimizer will take index into account.

Upvotes: 1

Go0se
Go0se

Reputation: 98

On MySQL, JOINS perform much faster than nested SELECT statements. Therefore, to start with, I would write a SQL statement like this:

SELECT friends.* FROM friends
INNER JOIN birthdays ON (friends.userid2 = birthdays.userid)
WHERE birthdays.birthday_date >= [start date] AND birthdays.birthday_date <= [end date]

You can then replace [start date] and [end date] with todays date, or a range of dates, like this week, to return the appropriate records.

Upvotes: 0

Gopal Joshi
Gopal Joshi

Reputation: 2358

Try Below Query Example

SELECT friends.*
FROM friends inner join birthdays on friends.userid2 = birthdays.userid 
WHERE birthdays.birthday_date='05/02/2014'

Always use datetime datatype for date values.

Upvotes: 0

Related Questions