Search code examples
mysqlsqlselectinner-join

Mysql INNER JOIN WHERE takes too long

My double Inner Join sql query takes very long (>60 seconds). Anything I did wrong here? How can I improve it?

SELECT coasters.coaster_name, coaster_models.model_id, models.model_name 
FROM coasters 
INNER JOIN coaster_models ON coasters.coaster_id = coaster_models.coaster_id 
INNER JOIN models ON coaster_models.model_id = models.model_id 
WHERE models.model_name = "SLC"

The Explain you asked me for

I just made this statement and now it shows 8 entrys or more for something that is just a single coaster.

SELECT coasters.coaster_name, manufacturers.manufacturer_name FROM coasters INNER JOIN coaster_models ON coasters.coaster_id = coaster_models.coaster_id INNER JOIN models ON coaster_models.model_id = models.model_id INNER JOIN manufacturers ON models.manufacturer_id = manufacturers.manufacturer_id WHERE manufacturers.manufacturer_name = "Vekoma"

Output:

Abyssus | Vekoma
Abyssus | Vekoma
Abyssus | Vekoma
Abyssus | Vekoma
Abyssus | Vekoma
Abyssus | Vekoma
Abyssus | Vekoma
Abyssus | Vekoma
Accelerator | Vekoma
Accelerator | Vekoma
Accelerator | Vekoma
Accelerator | Vekoma
Accelerator | Vekoma
Accelerator | Vekoma
Accelerator | Vekoma
Accelerator | Vekoma
etc...
Solution

  • be sure you have proper composite (and someway rendendat) indexes on

    table coaster  column coaster_id, coaster_name 
table coaster_models column coaster_id, model_id
table models model_name, model_id,