On Freitag 13 Februar 2009 Paul J Stevens wrote:
> It didn't get lost. I'm eagerly awaiting patches suitable for merging
Yeah, me too :-))
But there are open questions that need an answer before any patch can be
thought of. Paul, you know the code, so I guess you can answer these.
Example:
*Side note*:
Aren't most searches of the form "%searchstring%"? If yes,
the index is useless, as you can only search "searchstring%" to use the
index. And if the index can't be used, it could be dropped altogether. I
did some searches with Kmail, the string I searched for shows what I
told Kmail to search (ZMI_*, e.g. ZMI_SUBJECT searched for subject)
[snip]
2) Every single search was of the form "%searchtext%", so no index could
be used. I mean, in dbmail 2.2 the index
»dbmail_headervalue_3« btree ("substring"(headervalue, 0, 255))
was never actually used and is therefore useless. And I guess 99% of all
searches will be performed with "where X contains Y", and that's why it
will have to be searched as X ILIKE %Y%. We could drop the index and the
speed wil be increased, as a never used index only costs performance.
Or is there any client that performs a search like Y%? I guess no. At
least, when I search for messages I always search "where X contains Y"
and not "where X begins with Y".
3) Searches are performed only on substring(headervalue,0,255). The
whole "headervalue" is never actually searched, and nobody seems to have
found that. This means either
a) people don't search in such long headervalues
or
b) people were searching but not finding messages and wondering, still
they didn't report because they can't believe the computer is wrong.
Is it true that we only search the first 255 chars anyway? Then get rid
of the longer headervalues.
If we want to search >255 chars in headers: is the index useful at all?
If %search% is used all the time, no index can be used and therefore be
dropped. Starting from PostgreSQL 8.3, a full text index could be used
http://www.postgresql.org/docs/8.3/static/textsearch.html
But I guess we don't want database specific searches?
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
