I seem to recall reading something similar on the mysql lists. I am
quite certain that the order of the fields in an index is critical,
particularaly if you're sorting or grouping results.
Just got done optimizing some queries, so I'll just paste some notes
in here:
When a query includes a GROUP BY clause, you should almost always
have an index that the database can use. When adding a multi-column
index for a group by query, put the group by column last. For instance:
SELECT * FROM foo WHERE lastName='Smith' AND firstName='Bob' GROUP BY
departmentId
We're filtering by firstName, lastname. Grouping by departmentId.
For the above query, add the following index:
alter table foo add index byname(lastName, firstName, departmentId);
This will make a HUGE difference in your query speed. Use the
EXPLAIN syntax to determine what's causing a slowdown in a query
(this may be specific to MySQL):
mysql> explain select * from timetracked where startTime>'2007-01-01'
group by descriptionID;
+----+-------------+-------------+------+---------------+------
+---------+------+-------
+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------
+---------+------+-------
+----------------------------------------------+
| 1 | SIMPLE | timetracked | ALL | NULL | NULL |
NULL | NULL | 25724 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+------+---------------+------
+---------+------+-------
+----------------------------------------------+
Red flags are: Using temporary, Using filesort. If you see these,
they're slowing down your query. Maybe you can index to fix it.
Good things to see are: available index, key=[not empty], and ref=Const
On Jul 11, 2007, at 5:08 PM, Q wrote:
On 12/07/2007, at 7:29 AM, Miguel Arroz wrote:
Hi!
I'm looking and the generated SQL from an EOAndQualifier, and it
looks like the order of the "anded" stuff is the inverse one I
write on the code. If I write a qualifier like "bananas = %@ and
apples = %@ and oranges = %@", the generated SQL will be the
reverse (t0.oranges = (...) AND t0.apples = (...) AND to.bananas =
(...)".
Is this normal? Is the order random? What's happening here? I'm
asking this, because from what I understand form the PostgreSQL
docs, the order of the stuff in ANDs (and ORs) may have cause a
really big difference in performance when indexes (and multi-
indexes) are envolved.
Where exactly did you read this? Order of the conditional
expressions shouldn't make any difference to the query planner.
--
Seeya...Q
Quinton Dolan - [EMAIL PROTECTED]
Gold Coast, QLD, Australia (GMT+10)
Ph: +61 419 729 806
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/sam%
40360works.com
This email sent to [EMAIL PROTECTED]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [EMAIL PROTECTED]
