Hi Alec, thanks for this answer. a) My column is not involved by query b) But it is necessary to hold it in the database (and I don't want to set file_priv) c) And it's as long as a whole document, this means 5-10K I think, a Primary Key is as good as an Index. I will use one table for every type of object, filled with my Data and ...hopeful... will succeed! Thanks a lot,
Jan ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, April 26, 2002 3:19 PM Subject: Re: Performance Select Table/Where > > I definitely think that, provided it is properly indexed, MySQL should NOT > load > the whole table in order to perform a well phrased query. That is the whole > point of > indexing the database - MySQL can go straight to the results. > > How large is your "large" blob? If it is large, and not involved in any > searching, you > might consider putting that in a separate file. Add an autoincrement column > and > put the XML in a file whose name is the auto-increment number. If this > enabled > yout records to become fixed size, my *guess* is that the speeding up of > the search > would compensate for a lot of overhead inopening the files for *small* > numbers of hits. > > > Would anybody like to comment what would be the size of blob that would > make > the above strategy sensible? Mu pure gut feel would be a few K. Smaller > than > 1K - keep in database. Greater than 10K - put in file. > > Alec > > > Your message------------------------ > > 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
