CODEWITHSUNDEEP
sqlmysqlquery-optimization
Vijay Bhatia
Vijay Bhatia

Reputation: 55

Mysql Query Optimization

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.

Upvotes: 0

Views: 80

Answers (3)

Adam Robinson
Adam Robinson

Reputation: 185643

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.

Upvotes: 0

Demitrius Nelon
Demitrius Nelon

Reputation: 1248

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

Upvotes: 4

AndreKR
AndreKR

Reputation: 33678

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

Upvotes: 0

Related Questions