CODEWITHSUNDEEP
mysqljoinindexing
Bogz
Bogz

Reputation: 565

MySQL JOIN Query Index optimization

I have this query. Assuming no index is created yet, how can i optimize this using index so the query runs faster? How do i create the index?

select c.company_name, c.company_id FROM companies AS c 
JOIN users AS u USING(companyid) 
JOIN jobs AS j USING(userid) 
JOIN useraccounts AS ua USING(userid) 
WHERE j.jobid = 10;

Any help would greatly be appreciated.

Upvotes: 0

Views: 3235

Answers (1)

Barmar
Barmar

Reputation: 780673

You should have indexes on all the columns used in the JOIN clauses. You can create the indexes either with CREATE INDEX or ALTER TABLE commands:

CREATE INDEX u_companyid ON users (companyid);

ALTER TABLE users ADD INDEX (companyid);

Note that primary keys of tables are already indexed, you don't need to add an index explicitly.

Upvotes: 1

Related Questions