> > i have one table with lot of index (around 30, record around 20 > millions) > > 30 indices. And they all are useful? How selective are they? >
Yes all are necessary :( but i think i will start a new thread to speak about the index because i have somes more questions that can be interresting for someone else > > > We access the data only throught query like > > > > select First 200 skip 0 .... from MyTable where c = XY and d = zw and A > > > x and b< Z ... ORDERBY C, D > > > > what is the best : > > Take an eye on the execution plan, I/O stats in the monitoring table or > via the trace api. > > Btw, you know that with the usage of FIRST above, you probably won't get > your expected result? > > You probably want to do: > > select first 200 skip 0 ... from ( > select ... from order by c, d > ) > hmm not understand where is the difference ? > > 1/ One table with lot of record ? > > 2/ Several tables with fewer records ? > > Things are getting more complicated with several tables IMHO. So if you > don't have a strong reason e.g. maintenance or performance wise, go with > one table. > > Basically, Firebird can handle tables with 20 millions records. It > vastly depends on how optimized the access is. > > > because i can split easyly the number of row in 10 differents tables > > > > also is an empty on not very used table use firebird ressource ? > > Sorry, I don't understand this question. > 1. ok, let say i m a real estate website and i have properties to sell in all the world. For this i must permit user to see all properties in one location (for exemple New York) but also in the location inside (Exemple Manhattan) For this i setup my location ID as a string like this : for US > NEW YORK(State) > NEW YORK(City) the id will be US-0001234-0001245 for US > NEW YORK(State) > NEW YORK(City) > Manhattan the id will be US-0001234-0001245--0001298 etc... now in my query if an user want to see all the property in manhattan i simply do Where Location like 'US-0001234-0001245--0001298%'; AND to see all the property in new york city (including manhattan) Where Location like 'US-0001234-0001245%'; AND to see all the property in US Where Location like 'US%'; so it's OK BUT the probleme is that i need also to add some other filter criteria (like nb_room, surface, etc..) and for query like where Location like 'US%' and nb_bedroom > 2 and nb_bedroom < 3 i m in trouble because of the "like" i can not use the multiple column index on (LOCATION, NB_BEDROOM) ... i can use only the single column index on location + single column index on nb_bedroom (and believe me speed is not the same) so for this i simply "duplicate" the row in the datase! For every property with Location like 'US-0001234-0001245--0001298' i create 4 row in the database with theses locations Row 1: US Row 2: US-0001234 Row 3: US-0001234-0001245 Row 4: US-0001234-0001245--0001298 in this way i can easyly uses my multiple column index on the query where Location = 'US' and nb_bedroom > 2 and nb_bedroom < 3 but now you understand ... i can have only one table for all the row OR to split theses row in 4 differentes tables TABLE_LOCATION_LEVEL1 Row 1: US TABLE_LOCATION_LEVEL2 Row 2: US-0001234 TABLE_LOCATION_LEVEL3 Row 3: US-0001234-0001245 TABLE_LOCATION_LEVEL3 Row 4: US-0001234-0001245--0001298 but now before to do so i just need to know about the cost of having multiple table instead of one big single ! Theses tables will be also in different database / server to reduce the charge on the main database [Non-text portions of this message have been removed]
