What about partitioning the database?
On 7/1/06, Dan Buettner <[EMAIL PROTECTED]> wrote:
Wow, that is a tough one. My question would be, how often is older data really accessed? Could you start incorporating a default date range like "past 3 months" or "past 1 year" into all searches, but allow people to override it if needed? Then if you add an index on the timestamp column it would help any searches with a date clause. Dan On 6/30/06, Martin Jespersen <[EMAIL PROTECTED]> wrote: > 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) > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]