Hi creating all combination of index is not good solution maybe my tracker request will be good for you if yes vote on it http://tracker.firebirdsql.org/browse/CORE-2795
but in your scenario will be good also shardering simple way for simple shardering solution will be divide data between tables Region 1 , Region 2 ... Karol Bieniaszewski --- In firebird-support@yahoogroups.com, Vander Clock Stephane <svanderclock@...> wrote: > > > > > 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 > > > > Stupid question, maybe, but why not use postal codes? - they're predefined > > and denser. At least in the US, codes tend to be well-localized, meaning > > that 02139 is adjacent to 02138 and both are in Cambridge Mass. > > > > it's not postcode but just geographical ID > every location have a unique integer ID > and we add secondary string ID based on the id of their parent > > Manhattan iD is 0001298 (integer) > manhattan secondary ID is US-0001234-0001245--0001298 (string) > > > > > > > > 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%'; > > > > > > > OK. This query has one of two problems. The more likely is that you're > > not using parameterized queries. Parameterized queries are queries where > > you prepare the query without the input values then execute sending just > > the values. That saves the cost of parsing, compiling, and optimizing the > > query over and over again. > > > > yes but i think this is negligeable no ? > > > > > > The second, and less likely, is that you're using LIKE with a parameter, > > which means that the condition can't use an index because the parameter > > could start with a '%'. Instead of LIKE use STARTING WITH (or is it > > STARTS WITH?). > > > > it's always STARTNG WITH > > > > > > > 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) > > > > Right. But you could use an index on (NB_BEDROOM, LOCATION), if > > you were prepared to build the query as NB_BEDROOM = x and LOCATION > > starting with y OR NB_BEDROOM = x+1 and .... > > > > yes but unfortunatly it's can work with nb bedroom but it's will not work > with fields like price or surface for exemple because the delta min vs > max is much > more bigger :( > > > > > > > > so for this i simply "duplicate" the row in the datase! > > > > > > > Arrggg!!! It's well known that if you've got two copies of the same > > data, at least one is wrong. Having four copies of every piece of data > > is ... well, not generally considered good design. > > > > i know, i know ... but except "denormalyzing" the database i don't see > any solution to my probleme :( > > > > How about having separate fields with each part of the location, so if > > you know you're > > looking for a specific block in Manhattan, you look for > > > > LOCATION_COUNTRY = 'US' and > > LOCATION_CITY = <n> and > > LOCATION_ARRONDISMENT = <m> and > > LOCATION_CARRE = <q> > > > > If you need less detail, ask for fewer fields. You'd have more > > indexes, but if the read load is substantially heavier than the update > > load - which I'd guess it is by 100:1 or more - then more indexes > > aren't that much of a problem. > > > > i actually think about this, but dedpendly of the country sometime their > is more than 10 level ! > ex: United States > Alaska > Aleutian Islands > Aleutian Islands > Fox > Islands > Krenitzin Islands > Akutan Island > Akutan > > so i will need to add at least all this field : > > location_level1 (country) > location_level2 > location_level3 > location_level4 > location_level5 > location_level6 > location_level7 > location_level8 > location_level9 > location_level10 > > but this will not help my query like > > where > location_level3 = 0023954 and > Price > 100000 and > price < 200000 > order by > location_level3, CREATION_date > > with index to use ?? multi column index on > (location_level3, Price) OR > (location_level3, Creation_date) ? > > that ok, i have actually the same probleme in my design > but here i will need also to create too much of index to answer all the > possible case ! > > (location_level1, Price) > (location_level1, Creation_date) > (location_level2, Price) > (location_level2, Creation_date) > ... > (location_level10, Price) > (location_level10, Creation_date) > > > in my design where i duplicate the row i can have query like > > where > location = 0023954 and > Price > 100000 and > price < 200000 > order by > CREATION_date > > and only need to create index like > > (location, Price) > (location, Creation_date) > > > [Non-text portions of this message have been removed] >
