0

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...
Hen
  • 11
  • 3
  • Since these are inner joins and your only filtering criteria is on `models`, logically `models` should be the first table after from. – Stu Jun 11 '21 at 12:06

1 Answers1

0

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,
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • My tables have these exact columns in them and im using them in my sql query – Hen Jun 11 '21 at 11:56
  • be sure you have indexes on these column .. if you have not indexes then you have poor performance .. otherwsie if you have indexes then you should have fast query and good performance .. eventually take a look at mysql reference for index – ScaisEdge Jun 11 '21 at 11:57
  • Thank you very much, I added an index to the coaster_id and it now only takes 0.00sth seconds :) Which columns should I add indexes too and do they have any disadvantages? – Hen Jun 11 '21 at 12:03
  • @Hen but are they *indexed*? Post the index creation statements in the question. – Panagiotis Kanavos Jun 11 '21 at 12:05
  • You should use in index only the columns involved in join and where clause ..sometime sis useful add also the column in select clause in this way the query can retrieve all the data in the index and don't need a table access – ScaisEdge Jun 11 '21 at 12:05
  • @Hen the column order in the index matters too. – Panagiotis Kanavos Jun 11 '21 at 12:06
  • @Hen well if my answer is right please (past 15 minutes) mark it as accepted ...see how here http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – ScaisEdge Jun 11 '21 at 12:07
  • I just have one more problem (I added it to my question). Now that it doesn't take long anymore it selects some coasters up to 8 times. Any idea why that is? – Hen Jun 11 '21 at 12:14
  • if you have 8 entry the query return 8 rows ,,in this case if you need distinct result use `SELECT DISTINCT coasters.coaster_name, manufacturers.manufacturer_name.......` – ScaisEdge Jun 11 '21 at 12:16
  • Thank you that helped, im marking it as accepted :) – Hen Jun 11 '21 at 12:21