Hi Li Li,
>
> I'm thinking about separating a table with up to millions of rows into
> several tables with the same set of columns to speed up some complex
> queries.
I thought of doing this recently, as queries were taking so long. Instead of breaking the table up, we clustered the data. This physically moves all the data by key close to each other on disk (sounds kind of like defragging a disk). This boosts query responses no end - for example our table has ~ 10 million rows, a query that was taking 45 seconds to return, now takes 7 seconds. To keep the table tidy, we run the cluster regularly.
> As the size of the original table is increasing fast, I want
> to get it separated once the size grows up to a limit. So there
> will be a large amount of tables (having same structure) in a
> database. Is
> there any potential performance problem with this design?
>
I think the problems would mainly be in management, as you would have to keep track of the new table names, key names, and index names.
Nikk