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
