Mysql Query Optimization

Question

I have two tables : table1 has 4lacs (400,000) records and other have 100 records.

I am trying following query :

SELECT DISTINCT t1.id as id 
FROM table1 t1 
JOIN table2 t2 ON t2.f_id = t1.id 
              AND  t1.action = 0;

I am using MYISAM Engine. t1.id and t2.f_id are same and indexed.

this query is taking 30 secs. Is there any way to make it faster.

Demitrius Nelon · Accepted Answer · 2011-01-06 14:36:47Z

4

Be sure to have an index on table1.id, table1.action and table2.f_id, and assuming no other indexes live on either table that are causing performance issues, this should be closer to optimal:

SELECT
     DISTINCT t1.id AS id
FROM
     table1 AS t1
     JOIN table2 AS t2 ON t2.f_id = t1.id
WHERE
     t1.action = 0

answered Jan 6, 2011 at 14:36

Demitrius Nelon

1,2681 gold badge11 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AndreKR · Accepted Answer · 2011-01-06 14:36:14Z

0

Set one index on t1.action,t1.id and another one on t2.f_id.

answered Jan 6, 2011 at 14:36

AndreKR

34k21 gold badges120 silver badges181 bronze badges

Comments

Adam Robinson · Accepted Answer · 2011-01-06 14:37:30Z

0

The first step would be to add an index on table1 that covers id and action together.

Introducing distinct into the query is going to slow it down as well.

answered Jan 6, 2011 at 14:37

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Collectives™ on Stack Overflow

Mysql Query Optimization

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related