Sergej Shablakov wrote:
> SELECT DISTINCT
> records.id,records.author as author,records.created as created,records.modified as modified,
> t1.value,t2.value,t3.value
> FROM `records`
> LEFT JOIN `String_data` as t1 on (records.id = t1.record_id) and (t1.field_id = "1")
> LEFT JOIN `String_data` as t2 on (records.id = t2.record_id) and (t2.field_id = "2")
> LEFT JOIN `String_data` as t3 on (records.id = t3.record_id) and (t3.field_id = "3")
> GROUP BY records.id
Why are you using DISTINCT and GROUP BY (there a no aggregate functions in your SELECT).
Isn't (String_data.record_id, String_data.field_id) unique (i.e. one value per record per field_type)?
If so, you should set it as a composite primary key.
Each LEFT JOIN should be of eq_ref type with only one row as result.
What does EXPLAIN SELECT .... \G tell you?
If field_id is NUMERIC (INTEGER), there should be no quotes around the literal, e.g.
t1.field_id = 1
--
felix
Please use
BBCode to format your messages in this forum.