On 24/03/06, tedd <tedd@xxxxxxxxxxxx> wrote: > > Storing things in MySQL should not be based upon "If you're going to > search the field or not". MySQL storage is simply a way to store > stuff. It's not that much different than storing things on the "file > system" because all you're storing is 1's and 0's anyway, right? It > should not make any difference if file's 1's and 0's are stored on a > file system's hard drive or the files 1's and 0's are stored in a > MySQL dB, which is also stored on a hard drive, right? > > Remember, the only difference here is the overhead architecture of > how to access the data -- there are no differences between 1's and > 0's depending on where they are stored on a hard drive. > > Now, one can argue that the time it takes to pull an image from MySQL > is different than from the file system, I've experienced that, and > it's important to me, and I will be changing my method to the file > system. > > However, using MySQL for storing things does present some advantages > -- like associating related but different data types together data in > one record (it's hard to do that using a file system); moving data > from one server to another (again, it's hard to do that with a file > system); easier record keeping and editing; and if my memory serves > me right (which may be in error), there is a limit to the number of > records you can have in a directory whereas that isn't so in MySQL > (memory permitting of course). The technical limit per directory in ReiserFS is slightly more than half a billion files. Although with the standard hashing function the practical limit is about 1.2 million. Would you like to hazard a guess about MySQL's performance with 1.2 million images in the same file? MySQL stores each table in it's own file which is subject to the filesystem size limits. For example the maximum file size in ReiserFS 3.5 is 2 Gig. So ignoring all the overheads, to fit more than 1.2 million images in a table, they'd have to average rather less than 2kb in size. Each of these images is likely to be a different size so you're stuck with variable length records, which has a significant performance impact in MySQL. It'll also result in gaps in the file which will further restrict the amount you can get in there. You'd better make sure your indexing on the table is good, because a sequential scan over that amount of data will give your disk IO a good workout. If you'd kept all your files in the filesystem you'd be able to leverage all the efficient tools honed over the years to manipulate them, and copying them to another server would involve just a simple rsync. If you need to associate the files with a particular record, all you need to do is store the filename. It's not rocket science. At the physical level, you're correct; It's all just binary data. At every other level there's a big difference between using the filesystem directly and bunging everything in MySQL. -robin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
