2006/6/21, Beermann, Albert <[EMAIL PROTECTED]>:
Hello Everbody !
I our application we have a search form where the user can fill up to 6
fields.
Then we create a dynamic select command depending on the filled fields
an pass it to a maxdb datbase.
Select .... From tablex
Join ....
Join
Join
Join
Where tablex.field1 like '%var1% and tablex.field2 like '%var2%' and
tablex.field3 like '%var3%'
The "select part" is allways the same.
The "where part" of the command is dynamicly build from the fields the
users filled in our search form.
Field1 is allways filled, field2 to field6 can be filled in any
combination but don't have to be
Where field1 like '%var1%'
Where field1 like '%var1% and field3 like '%var3%'
Where field1 like '%var1% and field4 like '%var4%' and field6 like
'%var6%'
...
Tablex has 5 million records
The tables that are joined have between 100000 and 2 million records
Selects are slow !
We have to work with like ! (we search for names, post codes ...)
If you have to work with LIKE and allow leading placeholders no
indexing of the world will help as the DB has to do a table scan
anyway. You might gain a bit by having an index on the field because
the index needs less # pages on disk than the table but I guess it
won't be much.
You might be better off with a text retrieval system which is
optimized for text pattern search. Or you create a combination of DB
and such a system where this system emits a number of keys that you
can use to select from the RDBMS.
What kind of index would speed up the search.
One index on each of the 6 search fields ?
Combination index (field1 + field2 + field3 ...)
Combination indexes with field1? (field1 + field2) (field1+field3)
(field1+ field4)
Actually you will have to test it out. Nobody can answer that - and
the answer might even change with product versions. In the end your
overall optimal solution might involve one or several multi column
indexes and additional single column indexes - which combination is
the most efficient is determined by the distribution of data and
frequency of select criteria.
Personally I would not tackle such a task with MaxDB. Other databases
are more expensive but offer much more control and options for
optimization - even SQL Server does. And they are more disk efficient
(both space and speed). At least that's my experience.
Kind regards
robert
--
Have a look: http://www.flickr.com/photos/fussel-foto/
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
