i have problem with fine tuning my query, so I'm asking for help.
There are three tables:
1.Request:
| ID | User_ID |
|---|---|
| 209384 | 1000 |
| 209385 | 1001 |
2.equipment_type:
| ID | description |
|---|---|
| 1 | stove |
| 2 | boiler |
| 3 | heater |
3.user_to_equipment_type:
| ID | User_ID | equipment_type_id | Model |
|---|---|---|---|
| 1 | 1000 | 1 | Model1 |
| 2 | 1000 | 2 | Model2 |
| 3 | 1000 | 3 | Model3 |
| 4 | 1001 | 1 | Model4 |
| 5 | 1001 | 2 | Model5 |
I need to show everything about the equipment of one user in one line. This is the part of the query that gets good results:
SELECT
request.id,
eqt.description as "type 1",
et.model as "model 1",
eqt2.description as "type 2",
et2.model as "model 2",
eqt3.description as "type 3",
et3.model as "model 3"
FROM
request,
equipment_type as eqt,
equipment_type as eqt2,
equipment_type as eqt3,
user_to_equipment_type as et,
user_to_equipment_type as et2,
user_to_equipment_type as et3
WHERE
request.id = "209384" AND
et.user_id = request.user_id AND
et2.user_id = request.user_id AND
et3.user_id = request.user_id AND
eqt.id = 1 AND
eqt2.id = 2 AND
eqt3.id = 3
and I get this result:
| ID | type 1 | model 1 | type 2 | model 2 | type 3 | model 3 |
|---|---|---|---|---|---|---|
| 209384 | stove | Model1 | boiler | Model2 | heater | Model3 |
But I don't like how much this request loads the system. Is there a way to make this less resource intensive?
index is on the user_id.
ps.
"Join" will only make things worse
pps.
I'm new to mysql, so I won't be able to clearly answer many clarifying questions.
