MySQL JOIN Query Index optimization

Question

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.

Barmar · Accepted Answer · 2014-11-02 08:45:58Z

1

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.

answered Nov 2, 2014 at 8:45

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

6 Comments

Bogz Over a year ago

do you mean i must add index on users.company_id, jobs.user_id and useraccounts.user_id? if yes, am i missing other columns thats needed to be indexed??

Strawberry Over a year ago

@MarkBonnieVestil exactly

Bogz Over a year ago

@Barmar thanks man. How about indexing companies.company_id. Will it help for faster performance on my query above?

Barmar Over a year ago

Isn't that the primary key?

Barmar Over a year ago

Yes, I said that in the last line of my answer. Primary keys already have an index.

|

Collectives™ on Stack Overflow

MySQL JOIN Query Index optimization

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related