List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 16 2006 11:54am
Subject:Re: Accountability with MySQL
View as plain text  
> > > 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


Thread
Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLSGreen14 Mar
    • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLRhino14 Mar
    • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLRhino15 Mar
      • Re: Accountability with MySQLJames Harvard15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
    • Re: Accountability with MySQLRhino15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
      • Re: Accountability with MySQLStephen Cook16 Mar
    • Re: Accountability with MySQLRhino15 Mar
      • Re: Accountability with MySQLJames Harvard16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
      • Re: Accountability with MySQLMark Leith16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
      • Re: Accountability with MySQLJames Harvard16 Mar
      • Re: Accountability with MySQLAlec.Cawley16 Mar
        • Re: Accountability with MySQLMartijn Tonies16 Mar
          • Re: Accountability with MySQLAlec.Cawley16 Mar
          • Re: Accountability with MySQLJames Harvard16 Mar
          • RE: Accountability with MySQLGordon16 Mar
            • On MySQL Views (Re: Accountability with MySQL)Martijn Tonies16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
    • Coalesce (was: Re: Accountability with MySQL)Martijn Tonies16 Mar
    • True Propositions (was Re: Accountability with MySQL)Martijn Tonies16 Mar
  • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLSGreen14 Mar
      • Re: Accountability with MySQLMartijn Tonies14 Mar
  • Re: Accountability with MySQLRhino14 Mar
  • Re: Accountability with MySQLRhino14 Mar
Re: Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLRhino15 Mar
Re: Accountability with MySQLMartijn Tonies16 Mar
Re: Accountability with MySQLMartijn Tonies16 Mar