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
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________ 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]
