[ale] mysql advice

Mike Harrison meuon at geeklabs.com
Sat Jun 20 10:48:38 EDT 2009


> relatively modern hardware.  If they're all submitting complicated JOINS
> then you might have a problem.   Also, your DB engine comes into some play
> here.  If you're willing to sacrifice transactionalness you can go with
> MyISAM which is very, very fast and you might see some performance gains
> using it.  It's  not crash-safe.  Maria is an engine written by Monty

MyISAM is incredibly fast for reads.. less so for inserts and updates.
then Innodb slows down things, but allows for a steady flow of inserts and 
updates. best of all, you can mix table types within a database
as needed.

As for joins and such.. I feel with modern hardware, tables should be 
reasonable "flat" and fat (in many but not all cases), avoiding
joins as much as is practical. It of course, depends on the application 
and many many things, but if you lean that way with MySQL, reporting and 
such is much faster. Oracle, Sybase, DB2 etc.. seem to do joins faster.

I've found for a lot of reporting/output needs, doing odd things with 
temporary tables, in 2 or more steps is much faster and "safer"
that complicated joins. "Orphans" show up.. that, while they should not 
exist, sometimes will not show up in a join because someone deleted the 
matching key/record in another table.

   (yes, I know that if referential integrity is enforced.. etc.. etc..
    but there are exceptions to everything but death and taxes.. )

And now.. back to porting an ancient homegrown HPUX system data to a new 
Linux system running MySQL.






More information about the Ale mailing list