How to make an inner join efficient mysql

Question

I'm having two tables called 1. Table A - with multiple columns, having primary key as user_id (900,000+ records) 2. Table B - with single column called user_id (900+ records)

The table A contains all user_id 's of table B and more. I need to get a list of * columns of matching A,B rows by user_id. I'm using the below query, it has already run for 1 hour in my p4 machine with 100% CPU with no result in sight.

SELECT a.* FROM tableA AS a INNER JOIN tableB AS b ON a.user_id = b.user_id;

Is there a way to make the query execution fast?

biziclop · Accepted Answer · 2012-08-05 09:30:49Z

First ensure that both tableA and tableB has indexes on their user_id field.

If standard techniques don't help, you can force MySQL to join those two tables in a different order using STRAIGHT_JOIN:

SELECT a.*
FROM          tableB AS b /* first process the 900 rows */
STRAIGHT_JOIN tableA AS a /* and find matching rows for every tableB row */
           USING user_id; /* BTW, you could use USING here :) */

http://dev.mysql.com/doc/refman/5.0/en/join.html

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

Other info: http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html

... The table read order forced by LEFT JOIN or STRAIGHT_JOIN ...

For a LEFT JOIN, if the WHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to a normal join.

Collectives™ on Stack Overflow

How to make an inner join efficient mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related