On 12/07/2007, at 3: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:
While this might apply for your particular database, query and
dataset it's not a general rule that can be applied to any case. You
want to place the index fields in the order that will constrain the
row yield as quickly as possibly. In your case if by some strange
chance everyone had the same lastname it would make your index far
less useful than if you placed firstname as the first field in the
index. The point being that your focus needs to be on the likely row
yield and value frequency for each field constraint, not what the
group by column is.
SELECT * FROM foo WHERE lastName='Smith' AND firstName='Bob' GROUP
BY departmentId
This query is invalid, your database should reject it.
It should be:
SELECT departmentId FROM foo WHERE lastName='Smith' AND
firstName='Bob' GROUP BY departmentId
which is the same as writing:
SELECT DISTINCT departmentId FROM foo WHERE lastName='Smith' AND
firstName='Bob'
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):
Yes, most databases have some way of viewing the query planner
strategy. In mysql and PostgreSQL you use EXPLAIN.
Red flags are: Using temporary, Using filesort. If you see these,
they're slowing down your query. Maybe you can index to fix it.
When doing performance testing it's extremely important to use
realistic amounts of REAL data. Synthetic tests can yield very
different results to processing real world data.
Sometimes it can actually be slower to use an index than to do a
sequential scan on the database if you need to visit most of the row
in the table anyway.
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/qdolan%
40gmail.com
This email sent to [EMAIL PROTECTED]
--
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/archive%40mail-archive.com
This email sent to [EMAIL PROTECTED]
