MySQL Forums
Forum List  »  Performance

Re: Query Performance
Posted by: Felix Geerinckx
Date: October 03, 2005 02:28AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2018
October 03, 2005 12:17AM
Re: Query Performance
1343
October 03, 2005 02:28AM
1316
October 03, 2005 09:44AM
1394
October 03, 2005 11:08AM
1358
October 03, 2005 11:39AM
1277
October 03, 2005 11:50AM
1256
October 03, 2005 12:03PM
1256
October 03, 2005 12:32PM
1233
October 03, 2005 12:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.