> > > in order to retrieve all the information about them. Replacing a > > > theoretically ugly null flag with a 10 way join strikes me, as an > engineer > > > rather than a theoretician, the wrong side of the > elegance/practicality > > > trade-off. > > > > Using NULLs as well as de-normalization brings the risk of > > integrity problems to your storage, storing what is right is only > > a good thing. > > > > And when it comes to having to writing JOINs for all your queries, > > lo and behold, I bring you the wonder of the VIEW. > > > > ;-) > > The VIEW eases the syntax, but does it do anything for performance? Surely > it must be much slower to read 11 different tables (Master record > containing all NOT NULL fields, and 10 slave records which may or may not > contain relevant fields)? Ignoring caching, you are going to have at least > one disk access for every NULL field (index lookup which fails) and two > for every non NULL field (index lookup, data lookup) for every null field. > This means that you have multiplied your number of disk accesses (ignoring > caching, again) by 6-11 times (assuming the master record takes two disk > accesses). That again seems a very high price to pay for theoretical > elegance.
Keeping your design clean is hardly sacrificing anything. The most important feature of your database and the database design is the integrity of your data. Period. De-normalization, NULLs et all make this task much harder, as well as understanding your design for the guy that takes over your job ... Performance comes next, first thing to do there is getting your indices straight. Next thing is optimizing the slow stuff - if you can go around by keeping, for example, (these much smaller slave) tables in memory, and this works out fine, then do so. If all your queries are optimized, the next thing to do is to throw hardware at it. This is very cheap comparing to your hourly rate. ;-) Obviously, if your queries and design are valid and the database system is still slow, you can also complain with the creators of the database system -> performance, after all, is a physical attribute, not a database design issue perse. During the years, many database systems have seen many improvements to bring better performance to its users. Only recently, for example, the Firebird DBMS implemented a new on-disk structure for it's indices greatly enhancing performance for indices with a large number of duplicates. In the past, people said: you have to modify your design such and so, with all risks included. Now, you can keep your design as clear as possible and enjoy greater performance, just because someone thought of something clever. If no-one ever complained, this particular piece of code wouldn't have changed. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
