Mysql: Select data from two tables, where email address column matches in both

Question

I'm trying to get data from two tables they are wp_users, and opt_in.

The tables both have an email column (the only common between them), and I'm trying to get results that look like this, where I have the wp_users login name, and the rest of the opt_in table data:

wp_users.user_login, email, opt_in.first_name, opt_in.last_name

The query I've written is too slow. With around 100k rows in each table, I haven't get results without putting a limit on the query. I must not be using the right tool for the job. Show me the error of my ways.

Here's an example of the query which gives me the right data, but is very slow:

SELECT wp_users.user_email, opt_in.first_name, opt_in.last_name 
FROM wp_users, opt_in 
WHERE wp_users.user_email = opt_in.email;

You might want to consider indexes on your tables to speed up your query... — Mr47, Oct 31 '12 at 08:35

score 2 · Accepted Answer · edited Oct 31 '12 at 08:50

2

You should create index on that column.

For email fields you can use UNIQUE index it will also help you to remove duplicate entries.

In case if you want to check existing index's on your table. Use below query in phpmyadmin / mysql console

show indexes from TABLENAME;

edited Oct 31 '12 at 08:50

Remy

12,555
14
64
104

answered Oct 31 '12 at 08:39

metalfight - user868766

2,722
1
16
20

You pointed me at the right direction, so I'll accept the answer. ALTER TABLE `wp_users` ADD INDEX ( `user_email` ) ; ALTER TABLE `opt_in` ADD INDEX ( `email` ) ; That's all I did... now it's running at light speed. Unique indexes couldn't be used, but that's OK. Thanks – fsckin Oct 31 '12 at 15:57

score 2 · Answer 2 · edited May 23 '17 at 10:33

Try using JOIN - http://dev.mysql.com/doc/refman/5.5/en/join.html

SELECT wp_users.user_email, opt_in.first_name, opt_in.last_name
FROM wp_users
INNER JOIN (opt_in)
ON (opt_in.email = wp_users.user_email)

use EXPLAIN to understand what's going on
and as mentioned, Indexes play a huge role in increasing speed of query execution
Few other links explaining why anyone should use JOIN instead of multiple Select
- SQL left join vs multiple tables on FROM line?
- Left join or select from multiple table using comma (,)

Mysql: Select data from two tables, where email address column matches in both

2 Answers2