On 22-12-2011 12:22, Vander Clock Stephane wrote: > 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
Are you really using concatenated strings containing several distinct dataitems as your key? Given your example the identifier for Manhattan is 27 bytes, if you would use a key of seperate fields you need a CHAR(2), and 3 INTEGERs so 2 bytes + 3*4 bytes = 14 bytes. Assuming that the identifier for NEW YORK(State) is globally unique, you probably don't even need to use the US prefix losing 2 bytes. This would almost halve the size of the required indexes and I would expect that to be faster for lookup than such a concatenated string. If you insist (or really need) to use such a concatenated identifier, you might be better off looking at a NoSQL solution (although I admit I don't know enough about NoSQL to substantiate that claim). > 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%'; Individual fields: WHERE country_code = 'US' AND state = 1234 and city = 1245 and area = 1298 > > AND to see all the property in new york city (including manhattan) > Where Location like 'US-0001234-0001245%'; Individual fields: WHERE country_code = 'US' AND state = 1234 and city = 1245 > > AND to see all the property in US > Where Location like 'US%'; Individual fields: WHERE country_code = '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 Individual fields: WHERE country_code = 'US' and nb_bedroom > 2 and nb_bedroom < 3 (BTW: I assume you are aware that that condition is always false, unless New York as fractional rooms ;) > 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 I think this problem would go away if you used individual fields. Mark -- Mark Rotteveel
