Re: Some help with Optimization
Re: Some help with Optimization
- Subject: Re: Some help with Optimization
- From: Guido Neitzer <email@hidden>
- Date: Fri, 10 Feb 2006 16:49:13 +0100
On 10.02.2006, at 16:18 Uhr, Ken Anderson wrote:
The bottom line is, I have not met a SQL optimizer that understands
WO's idea of how to lock optimistically. As far as it's concerned,
why would you put 20 fields in a where clause if you already know
that one of them is a unique primary key? So, it will run the
query through many stages of optimization, and often determine that
there's no good index to help it (because it thinks it's looking
for a result set larger than 1) and it will resort to a table
scan. Yes, the name implies exactly what it does...
I don't know which databases you have used, but at least PostgreSQL
does what it should do:
explain analyse select id, vorname, nachname, plz, ort from
dga_dienstleister where id = 1082780 and plz like '45257' and ort =
'Essen' and vorname = 'Michael' and nachname = 'Neuhaus';
'Index Scan using dga_dienstleister__id__pk_index on
dga_dienstleister (cost=0.00..3.16 rows=1 width=50) (actual
time=0.326..0.336 rows=1 loops=1)'
Index Cond: (id = 1082780)
Filter: (((plz)::text ~~ '45257'::text) AND ((ort)::text =
'Essen'::text) AND ((vorname)::text = 'Michael'::text) AND
((nachname)::text = 'Neuhaus'::text))
Definitely an index scan.
Frontbase does the same:
"SVI_LITERALS" = (
{
ESTIMATE = 1;
"INDEX_NAME" = "_I0000000014";
"ROW_COUNT" = 1;
"SCHEMA_NAME" = webobjects;
"SELECT_INFO" = ("ID = 1082780");
"TABLE_NAME" = "DGA_DIENSTLEISTER";
TIME = "0.013367";
}
Sure the filter condition may make a small overhead, but that's all.
A query optimizer must be VERY stupid if it sees a pk attribute in
the where clause and not using it for an index scan!
cug
--
PharmaLine, Essen, GERMANY
Software and Database Development
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden