CODEWITHSUNDEEP
mysqlperformancejoin
Mike Flynn
Mike Flynn

Reputation: 24325

MySQL Join Performance

The following query below executes in 17 seconds in a view. There are 450,000 rows. I have an index on the two columns being joined and they are FK. The join columns are BIGINTS. Is there anyway to speed this guy up?

SELECT c.id, sce.user_id
FROM sims_classroom c
JOIN sims_class_enrollment sce ON c.id = sce.classroom_id

EXPLAIN

'1', 'SIMPLE', 'c', 'index', 'PRIMARY', 'PRIMARY', '8', NULL, '211213', 'Using index'
'1', 'SIMPLE', 'sce', 'ref', 'fk_class_enrollment_classroom_id', 'fk_class_enrollment_classroom_id', '9', 'ngsp.c.id', '1', 'Using where'

ROWS

sims_classroom = 200100
sims_class_enrollment = 476396

Upvotes: 2

Views: 2237

Answers (1)

joshOfAllTrades
joshOfAllTrades

Reputation: 1982

It will slow down writes a little but since you're only one column short of having everything you need in your index, I would do a two column index for sce:

classroom_id, user_id

This would result in mysql not even needing to go to the actual table (both would be 'Using index' in the explain).

Upvotes: 2

Related Questions