[development] table vs tables

[Larry Garfield]

On Fri, 7 Sep 2007 09:51:00 -0600, "Steve Ringwood" <nevets at mailbag.com> wrote:

> Are you really planning on storing the photo's in the table.  It is
> generally more useful to store the image on the file system and just the
> path in the database (you just need the image path and not the image for
> the
> browser).  If you are not planning on placing the images in the database
> 100K is a lot of data, more than make sense to display on a single screen.
> 
> As for speed, one of the factors is a unique key for each record another
> though is how you plan to look stuff up.  Just the potential number of
> records is large (500,000) and almost twice as many comments a drupal.org
> has.  The "obvious" way to break up the table would be to use 1000 a
> smaller
> tables, but too many tables can also cause a problem.  The bottom line is
> it
> will take some tweeking to get things "right" and that will depend on the
> details of the data and how the data is accesed.
> 
> Steve

If you're looking up records by a numeric primary key (which is most things in Drupal) or by some other properly indexed value, 500,000 records is nothing.  A good RDBMS can handle millions of records.  The keyspace for the primary key, if it's an integer, is somewhere around 3 million.  Using an unsigned int or bigint will increase that even further.  

Just use the Drupal file API.  It puts the files on disk and the path in the files table, and should scale to hundreds of thousands of records just fine if you're using a recent version of MySQL.  

--Larry Garfield