CODEWITHSUNDEEP
mysqlperformanceinner-join
user1577111
user1577111

Reputation: 21

How to make an inner join efficient mysql

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?

Upvotes: 2

Views: 1321

Answers (1)

biziclop
biziclop

Reputation: 14626

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.

Upvotes: 1

Related Questions