It's basically a log that people needs to be able to search with
wildcards in... the log grows many thousand records per day and never
gets smaller, so searches just gets slower and slower. There is a sort
field, the timestamp which is used in the searches, but it only makes
the searches lsower yet instead of helping in the query, since all that
does is sort by timestamp desc
basically the query works like this:
some searches for "foo bar baz" and i create an sql that looks like:
select * from table where logline like '%foo%bar%baz%' order by
timestamp desc. I have wrekced my brian plenty but have not come up with
any otehr way of doing it that gives the needed flexibility in the
searces. Since what is searched for is not words as such - most loglines
are actually a single "word" on the form
"something<specialchar>something<specialchar>something<specialchar>something<specialchar>something<specialchar>"
and so on - the logline is varibale length and variable number of
"entities" between the sepcial chars (even the special chars are very
varied) and of no specific format, thus the needed flexibility in the
searches.
If i coud i would changes the log format, but that is not possible since
this database has loglines going all the way back to the 1980's (with
more "old" lines being added as well as new ones) and the format has
changed many times since then...
Basically i am stuck with a very crappy heap of data i need to be able
to search in a smart manner.
Fulltext seaching would have been ideal if i was able to do boolean
macthes with leading wildcard, but without it is useless :/
btw the result doesn't need scoring for relevance at all - what is
searched for is always the newest matches to the searchterm, regardless
of relevance (relevance could become handy at a later stage tho, but i
dare not even think about it atm)
Dan Buettner wrote:
Bummer, Martin.
What more can you tell us about your specific application? What are
you storing and searching on, exactly? Any chance you can filter on
another column to reduce the number of records that need a string
search?
Dan
On 6/28/06, Martin Jespersen <[EMAIL PROTECTED]> wrote:
I was too fast there it seems
fulltext searches doesn't help after all since i can't use leading
wildcards to words :(
too bad i loved the speed :/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
