Re: data design question

[Egor Egorov]

Justin French <[EMAIL PROTECTED]> wrote:

> Should I worry at 40,000? 100,000?  Or will the indexing of the siteID 
> keep everything extensible?

Indexing is designed to keep SELECT speed small with no matter how much data it
is. You should ensure that the siteID index is properly used (use EXPLAIN
SELECT). Say, if half of the records belongs to a particular siteID - the index
won't help, the full table scan will be done to find out the needed article. 

You may consider creating a complex index as well - say, (siteID, articleID).
Then to fetch the list of all articles of a site the (siteID) index will be
used, and to fetch particular article the compound index will be used. 






-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

data design question

[Justin French]

Hi all,
I'm in the planning phase of a hosted web application where all 
instances of the app (a sort-of website CMS) will be running off a 
single code source.  I've got a clear picture about everything except 
for the database design.

a) I could have a separate database & table structure for each website 
in the application

b) I could have a single database for all instances, but individual 
table structures for each (eg client_tablename)

c) I could have all data from all instances in one table structure, 
with a website_ID for each record, signifying which site the record 
relates to.

I'm leaning towards (c) on the basis that updates to the database and 
table structure will be a breeze (only have to update one instance), 
but I'm concerned about performance.

Let's say I had 20,000 articles belonging to 100 websites.  Would there 
be a performance loss by having all 20,000 articles in one table, with 
a indexed `siteID` column identifying which site each article belongs 
to, as opposed to 100 tables (one for each site) holding only their own 
data??

Should I worry at 40,000? 100,000?  Or will the indexing of the siteID 
keep everything extensible?

---
Justin French
http://indent.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]