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