big table or several littles tables ?

[olivier]

> User enter you some city for search.
> What you will do next if you have 10 tables?
>
> IF you have way to search only ONE OF 10 tables,
> Then of course you will get win.

Yes naturally, If I divide into ten my big table, each of these 10 
tables will correspond to certain zip.
Thus according to the zip entered by the customer, the program will 
know about which table it will be necessary to make the search.

What is the best optimization (speed of calculation + cache + ACCESS 
HARD DISK)?
40 tables of 20Mb? 10 tables of 80Mb? 1 table of 800Mb? (cache 20Mb ?)

>
> If you will do 10 searches on 10 tables, you loose

no.
but if I import a file of 1000 addresses and that I must check the 
streets of these 1000 addresses, I will make 1000 research each time in 
a different table.

e.g. :

Search street 1 -> in table 3
Search street 2 -> in table 3
Search street 3 -> in table 1
Search street 4 -> in table 10
Search street 5 -> in table 5
Search street 6 -> in table 8
Search street 6 -> in table 8
..........


> To get the most compact indexes for such db, you need after filling of
> contents, do REINDEX and assign big cache 30-50MB.
>
> Having many memory Valentina will be able build index with low
> fragmentation. Also after this do defragmentation.
>

ok, thank you !

olivier