That's the point: Users are added about 10 times less than "objects". But your objection about new columns is right, also about system performance. I would have said the main problem is on a query like "show all users with their last object" for an overview, this would be a very hard job (I think this was your "advanage of simplicity"). My problem seems to be solved, but I still think about my columns - every row will contain user, date and the object by itself as a large xml-blob. Maybe selecting (I think this means, at first, loading the whole table?) the query above would be very hard, too(at first time, I think about 1000 users, each 10 objects). Isn't it? Regards,
Jan ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, April 26, 2002 1:00 PM Subject: Re: Performance Select Table/Where > > > > My first two questions could not be answered, so, maybe I'll be luck w/ > > third. > > > > A performance question: If I have user numbers and "objects" belonging to > > them(e.g.bills), is it better to have one table "bills" with many rows or > to > > have one table for every user? This means, is it better to do a sql where > > query over one table or to select the table at first? > > I'll put my ill-informed 0.02 in here, in the hope of getting confirmation > or refutation from the better-informed. > > My impression is that creating tables dynamically per user or similar is > not regarded as good > practice. Generally your tables should be planned in advance. > > Also, OSes have limits on numbers of files open, so having huge numbers of > users risks running out > of file handles as yout system grows, if many queries end up overlapping. > > On the other hand, IF you can index on user (in your example), MySQL will > make a very good job of > checking only the relevant part of the table > > So my guess (and first attempt) would be the second approach. Which also > has the advanage of simplicity, > which is a virtue in its own right. (What if you want to add an extra > column? A nightmare to do over > thousands of tables, simplicity for one). > > Alec Cawley > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
