Sharing a Database Between Websites
I discovered by accident that I can link any website on my reseller account to one database. That would be far more convenient than working with six separate databases, and it would also cut down on file size overall, since there are certain tables that I share between websites. But my host warned me that a big database could increase query time and make it harder to update. I want to understand exactly how this works. Suppose I have two database tables and one website that's linked to both those tables. Suppose it takes one second to query those tables. Now if I add 100 tables, but my website still queries just two of them, will a query still take one second? Or will those additional tables slow things down, even though my website doesn't even make any reference to them? And will those extra websites make it take increasingly longer to add additional tables or modify existing tables? I would think my local database ought to be a good guide. I have over 100 tables in the same database on my computer, and things seem to work just fine. Of course, I realize things take longer online. My webpages do run a little slow, but I think that's because of some sloppiness in designing my database tables; that's something I'll just have to refine as I learn more about MySQL. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
David Blomstrom said: I discovered by accident that I can link any website on my reseller account to one database. That would be far more convenient than working with six separate databases, and it would also cut down on file size overall, since there are certain tables that I share between websites. But my host warned me that a big database could increase query time and make it harder to update. I want to understand exactly how this works. The problem would be large tables rather than large database. Traversing a table is what costs time. There would be a slight increase in the Operating System finding the files associated with each table, but this is a factor only with very large numbers of tables not 100. This all assumes MyISAM table type. I don't know about the others since they do share a single file. Suppose I have two database tables and one website that's linked to both those tables. Suppose it takes one second to query those tables. Now if I add 100 tables, but my website still queries just two of them, will a query still take one second? Or will those additional tables slow things down, even though my website doesn't even make any reference to them? And will those extra websites make it take increasingly longer to add additional tables or modify existing tables? I would think my local database ought to be a good guide. I have over 100 tables in the same database on my computer, and things seem to work just fine. Of course, I realize things take longer online. My webpages do run a little slow, but I think that's because of some sloppiness in designing my database tables; that's something I'll just have to refine as I learn more about MySQL. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
Your theory that more tables won't slow down a client if it doesn't access them is correct, it won't slow the client down. But it's slowing down the server that you should be worried about, not the client. The more tables you have, the more file handles MySQL has to have open along with caching the security information for the tables. 100 tables won't make a noticeable difference, but is your setup something that could possible grow to 1000 or more tables? Then you are going to hit limits of the operating system, thus the design is not scalable. Big tables will have slower queries. But big is relative. Provided indexes are used, querying a 100 record table vs a 10,000 record won't have a noticeable time difference, even though there is 100x difference in the size of the table. Some might think 100,000 records is a large table, it's not. There are advantages to one large table over many smaller tables. A large table will take advantage of query caching better (if you have query cache enabled). Lots of smaller table would probably cause the query cache to get flushed more often. Of course, difference strategies will apply depending on whether you are using InnoDB or MyISAM table types. I would focus on good database design, good performance will usually follow good design. On Mar 23, 2005, at 1:36 PM, David Blomstrom wrote: I discovered by accident that I can link any website on my reseller account to one database. That would be far more convenient than working with six separate databases, and it would also cut down on file size overall, since there are certain tables that I share between websites. But my host warned me that a big database could increase query time and make it harder to update. I want to understand exactly how this works. Suppose I have two database tables and one website that's linked to both those tables. Suppose it takes one second to query those tables. Now if I add 100 tables, but my website still queries just two of them, will a query still take one second? Or will those additional tables slow things down, even though my website doesn't even make any reference to them? And will those extra websites make it take increasingly longer to add additional tables or modify existing tables? I would think my local database ought to be a good guide. I have over 100 tables in the same database on my computer, and things seem to work just fine. Of course, I realize things take longer online. My webpages do run a little slow, but I think that's because of some sloppiness in designing my database tables; that's something I'll just have to refine as I learn more about MySQL. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
--- Brent Baisley [EMAIL PROTECTED] wrote: There are advantages to one large table over many smaller tables. A large table will take advantage of query caching better (if you have query cache enabled). Lots of smaller table would probably cause the query cache to get flushed more often. OK, thanks. I don't foresee my database growing to over 200 tables in the near future. How do I determine if I have query cache enabled? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
show variables like 'query%'; That will show you the setting for mainly your query cache. The query_cache_type will tell you if it's on or not. On Mar 23, 2005, at 3:08 PM, David Blomstrom wrote: --- Brent Baisley [EMAIL PROTECTED] wrote: There are advantages to one large table over many smaller tables. A large table will take advantage of query caching better (if you have query cache enabled). Lots of smaller table would probably cause the query cache to get flushed more often. OK, thanks. I don't foresee my database growing to over 200 tables in the near future. How do I determine if I have query cache enabled? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
