MySQL Join Performance

Question

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

How many rows in sims_classroom and sims_class_enrollment too — Matt Dodge
– Matt Dodge, Commented Jun 29, 2012 at 23:49
Could you also try profiling your query? SET PROFILING = 1; SELECT ...; SHOW PROFILE FOR QUERY 1; SET PROFILING = 0; — eggyal
– eggyal, Commented Jun 30, 2012 at 0:20

joshOfAllTrades · Accepted Answer · 2012-06-30 04:00:21Z

2

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).

answered Jun 30, 2012 at 4:00

joshOfAllTrades

2,08216 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

MySQL Join Performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related