>>> > in this table around 50 000 000 records (and 20 fields)
>>> >
>>> > this index are motly here because we need to handle search and order
>>> > for client in < 100 ms. so we need to index all possible
>>> > case
>>> > of search
>>>
>>> But ordering using index is quite slow on the table of that size, your
>>> quieries will usually be slower than 100 ms anyway.
>
>> by ordering using index is slow, you mean in the insert (updating the
>> index) or you mean in the select (using the select) ?
>
> Yes, SELECT. Can you try yourself ? Construct SELECT statement with WHERE
> clause that will return e.g. 150 rows,
> and execute it on your 50 000 000 records table, once ORDERed by index,
> once withou index, and compare the times.
> (and use different fields for search condition and for ordering)
> (for easy switching between ordering with/without index, use ASC/DESC in
> your order clause)

"first rows" could be faster when using the index though, can't it Ivan?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com