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
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
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
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
Hey all i am running mysql 4.1.20.
I have a table with about 2.5 million records and i have to do queries
on it that looks something like:
select * from table where field1 like '%some%thing%' order by field2
This is ofcourse very slow since it refuses to use indexes...
i have fairly large
Martin, currently there is not a way to optimize that particular type of query.
You might consider changing to fulltext indexes and searches instead,
as it could be faster, but it is a slightly different animal from LIKE
so may not fit your needs.
Also, perhaps this is good enough for your situation:
like 'some%thing%'
as opposed to
like '%some%thing%'
in this case, mysql can use an index on that column and filter out
everything that doesn't start with some. It's a start at least.
Peter
On 6/28/06, Dan Buettner [EMAIL PROTECTED] wrote:
Indeed fulltext searches was the cure i was looking for.
Queries went from over 116 seconds to less than half a second
thx for the tip ;)
Dan Buettner wrote:
Martin, currently there is not a way to optimize that particular type of
query.
You might consider changing to fulltext indexes and
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]
