big table or several littles tables ?

[Ruslan Zasukhin]

On 9/27/04 6:09 PM, "olivier" <vidal_olivier at yahoo.fr> wrote:

> Valentina 1.90 - RB 5.5.3
> cache 20 Mo.
> 
> Hi Ruslan and list,
> 
> I have a big db with a table of 800 Mb consisted of street names, and a
> table was joined of 45 Mb for zip and cities.
> 
> I have the possibility of cutting the table of streets in 5 or 10. (The
> purpose for the customer is to look for street names thus always in a
> particular zip / city).
> 
> To accelerate the searches and optimize the cache memory, it is better
> to keep a big table of streets or to have several of it?

> I think naturally that it is better there to have ten of 80 Mb than one
> of 800 Mb but I would like to be sure of it.

Depend how you will choose the table for search.

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.

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

> The table of streets will be only in mode reading.

> Thus only the speed of sorting / search is here important.

TIP.

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.

> If we have to make 1000 searches very quickly on street names, it is
> faster to use an only big table or to juggle between 10 small different
> tables?




-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------