extend via table VS extend via database

hi,
I'm using innodb engine. There are more and more rows in my table
recently, and
I've planned to extend the structure.
Assume I have testdb, the database, and testTab, the table, in ONE mysql.
There are two ways:
* extend via table, that is,
testTab -----> testTab_1
-----> testTab_2
-----> testTab_3

* extend via database, that is,
testdb/testTab ------> testdb_1/testTab
------> testdb_2/testTab
------> testdb_3/testTab

After the extend operation, all tables & databses will be at the SAME
mysql firstly,
then migration to newly-add other mysql on demand.

Which way is better?
Thank you

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: extend via table VS extend via database

--001636d3448d2131b5047e4df1e9
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Jan 29, 2010 at 9:52 AM, Cui Shijun wrote:

> I'm using innodb engine. There are more and more rows in my table
> recently, and
> I've planned to extend the structure.
>

Why do you want to split your table ? What problems are you trying to solve
?


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001636d3448d2131b5047e4df1e9--

Re: extend via table VS extend via database

--0016e6477a62c6e9ea047e869ea3
Content-Type: text/plain; charset=ISO-8859-1

If you have performance problems, and no slowlog, maybe it just isn't
enabled in your config ?

Your indexes may not have changed, but the size of your data has. A query
that does a full tablescan will run acceptably on a few thousand records,
but be damn slow on a few million - especially as soon as the size of the
dataset to be scanned exceeds the system's filesystem cache.



Find out what queries are going slow, and use explain to see what you can do
about it.

On Sun, Jan 31, 2010 at 3:46 PM, Cui Shijun wrote:

> I had checked slow log & indexes, but no slow log exists, neither
> did the indexes change since the tables were created.
> Besides, The way I deleted old data is drop some unused table. No
> query were performed to those tables when I do that.
> I guess something really "magic" had happened when mysql dropped
> tables in that situation. :-P
>
> 2010/1/31 Johan De Meersman :
> > the first things to look at are usually the slowlog and indexes :-)
> >
> > On 1/30/10, Cui Shijun wrote:
> >> Mainly performance issues & maintain cost consideration.
> >> I've experienced a performance regression a few days ago. The box
> >> running mysql got
> >> a continuous suddenly-high user% cpu, util I delete some old data( 20G
> >> ), which caused
> >> by several data migrations.
> >> To avoid the above situation, I decided to split the table, so that
> >> I can move some of them to
> >> other boxes if necessary, to keep the amount of data per mysql in a
> >> suitable range. This is
> >> certainly not a way of solve a problem completely, but might be a way
> >> that works before I figure
> >> out what had happened to mysql.
> >>
> >> 2010/1/29 Johan De Meersman :
> >>> On Fri, Jan 29, 2010 at 9:52 AM, Cui Shijun
> wrote:
> >>>>
> >>>> I'm using innodb engine. There are more and more rows in my table
> >>>> recently, and
> >>>> I've planned to extend the structure.
> >>>
> >>> Why do you want to split your table ? What problems are you trying to
> >>> solve
> >>> ?
> >>>
> >>>
> >>> --
> >>> Bier met grenadyn
> >>> Is als mosterd by den wyn
> >>> Sy die't drinkt, is eene kwezel
> >>> Hy die't drinkt, is ras een ezel
> >>>
> >>
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016e6477a62c6e9ea047e869ea3--