Yes, the order of fields in an index is critical to optimization.
But we are getting away from the original issue which was EOF generating
SELECT * FROM foo WHERE firstName='Bob' AND lastName='Smith'
when Miguel was expecting:
SELECT * FROM foo WHERE lastName='Smith' AND firstName='Bob'
I would expect query optimizers to render both of these selects as
equivalent, but won't be surprised to be shown I am wrong for some
databases.
Chuck
On Jul 11, 2007, at 10:03 PM, Sam Barnum wrote:
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/chill%
40global-village.net
This email sent to [EMAIL PROTECTED]
--
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
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]
