mysql eav_如何优化这个复杂的EAV MYSQL查询？

Is it possible to optimize this query I have written

I've created a sort of dynamic virtual database to give my users the ability to add custom fields without affecting the database structure. Here is a very simplified view of the stucture so far.

tables | columns

db_cases | caseid

db_structure | fieldname

db_data | fieldname, data, caseid

db_names | nameid

We can create a new field by adding a row to db_structure

Any data we wish to record is recorded to db_data.

Names are stored in db_names and the name_id is stored in db_data

I am trying to output the cases to a html table

Hopefully the rest is self explanatory, you can see how unefficient it is. Can I do the same thing via joins?

SELECT

case_id,

(SELECT data_field_value

FROM db_data

WHERE data_case_id = case_id AND data_field_name = 'casestatus'

) AS casestatus,

(SELECT forename_company

FROM db_names

WHERE name_id = (SELECT data_field_value

FROM db_data

WHERE data_case_id = case_id AND data_field_name = 'client1'

)

) AS client1_forename_company

FROM db_cases

Thanks

解决方案

Actually, Chibuzo is right. Start by deleting it :-)) But before, play with it a little, it's a good brain excercise, like chess or something :-)

select

case_id,

d_status.data_field_value as case_status,

d_client1_name.forename_company as client1_forename_company

from db_cases

join db_data as d_status

on d_status.data_case_id = case_id

AND d_status.data_field_name = 'casestatus'

join db_data as d_client1

on d_client1.data_case_id = case_id

AND d_client1.data_field_name = 'client1'

join db_names as d_client1_name

on d_client1_name.name_id = d_client1.data_field_value

I would expect these direct joins without subqueries to be much more efficient, though you'll have to test it - there are often surprises in optimizations.