Den 2011-08-23 12:16 skrev Vander Clock Stephane såhär: > > I have a database that's about 56 Gbyte and has 150 million or more > > records (each) in two of the tables. > > > > I find query performance to be very agreable, provided an index can be > > yes, but when you need order by, even with index it's not funny at all :( > did we need to use the index on the query filter or on the order by filter ? > this off course depend how many record the query filter will return ... > with few reccord returned better to use the index on the query filter, > with lot of > reccord returned better to use the index on the order by ... but this > the optimization > engine can not know by advance ....
Not sure what you mean here, but have you tired adding both an ascending as well as a descending index on all columns that are involved in (possibly descending) sorts? As far as I know, FB will use any relevant combination of single-column indices for equality where criteria. Are you sure you've got the temp space for sorting setup in a good way? Perhaps put it on a RAM disk or fast SSD? Is your page cache large enough? I'd recommend superclassic to be able to utilize a very large page cache, e.g. 20 Gbyte, that's shared for all connections. Classic has separate page cache per connection so cache can't be too large, and superserver has problems with multiple cpu:s/cores. > > So, provided all your queries will be able to use the index on > > Contact_ID, I think you will be fine. And if not, then there are > > probably better ways to handle the situation than your option 2, e.g. > > moving records to an archive table or "throwing hardware at it". > > I already split the big table in 56 "archived like" table to reduce > the size. for now the perf are ok, but what in one year with the growing > of data :( i know that this system will soon meet the limit of firebird > OR the server himself How many records do you anticipate to have in total, all archive tables included, within the lifetime of this system? What's the growth rate? What would be a relevant archive cycle? Do your users do selects regularly from archives or are they mostly interested in the "current" stuff? Kjell -- -------------------------------------- Kjell Rilbe DataDIA AB E-post: [email protected] Telefon: 08-761 06 55 Mobil: 0733-44 24 64 ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
