Optimize a query that uses multiple left joins in the same tables

Question

I came across a question that takes too long. A query has 50+ left joins between 10 or so tables. To give a brief overview of the database model, joined tables are tables that store data for a particular data type (for example: date_fields, integer_fields, text_fields, etc.), and each of them has a column for the value, an identifier for the data field, and ticket identifier. The request is built programmatically on the basis of a table of associations between the “ticket” and the “data fields”.

Union operators look something like this:

...FROM tickets t
LEFT JOIN ticket_text_fields t001 ON(t.id=t001.ticket_id AND t001.textfield_id=7)
...
LEFT JOIN ticket_date_fields t056 ON(t.id=t056.ticket_id AND t056.datafield_id=434)

When using the explanation in the query, the following is displayed:

1   SIMPLE   t       ref   idx_dataset_id                   idx_dataset_id  5   const   2871   Using where; Using temporary; Using filesort
1   SIMPLE   t001   ref   idx_ticket_id,idx_datafield_id   idx_ticket_id   5   t.id   5   
... 
1   SIMPLE   t056   ref   idx_ticket_id,idx_datafield_id   idx_ticket_id   5   t.id   8

? , . , t ( ) (2871) . ? , ?

Answer 1

antipattern Entity-Attribute-Value. , , - , .

, 50 . ( , ). , , .

: SQL.

, , .

SELECT ... FROM tickets t
INNER JOIN ticket_text_fields f ON t.id=f.ticket_id
WHERE f.textfield_id IN (7, 8, 9, ...)
UNION ALL
SELECT ... FROM tickets t
INNER JOIN ticket_date_fields d ON t.id=d.ticket_id
WHERE d.datafield_id IN (434, 435, 436, ...)

, , , , .

Answer 2

- :

SELECT ... FROM tickets as t  
JOIN ticket_text_fields as txt ON t.id = txt.ticket_id  
JOIN ticket_date_fields as dt ON t.id = dt.ticket_id  
WHERE txt.textfield_id IN (...)
AND dt.datefield_id IN (...)

, , , .
,