This post originated from an RSS feed registered with Web Buzz by Douglas Clifton. Original Post: So What Else is New? Feed Title: blogZero Feed URL: http://loadaveragezero.com/app/s9y/index.php?/feeds/index.rss1 Feed Description: Web Development News, Culture and Opinion Latest Web Buzz Posts Latest Web Buzz Posts by Douglas Clifton Latest Posts From blogZero

This blog isn't the only thing that has been languishing around here. After adding a new category to my resource index and populating it with 30 new resources I realized I had finally reached the 1000 review milestone.

Time to break out a bottle of the bubbly? Actually, I prefer San Pellegrino, especially the citrus flavored varieties like Aranciata (orange) and Limonata (lemon). Alas, I digress.

Last week we had large turnout for the October DC PHP Developers Group meeting, which featured a presentation on optimizing MySQL by Barry Austin of doBoard. Below I list some comments and my own suggestions on the topic.

MySQL Rules of Thumb

• Keep your columns as small as possible and always use an unsigned (tiny,small,int,...) AUTO_INCREMENTing primary key on your main tables.
• Foreign keys on secondary and ternary tables must match the PK size and type.
• Avoid text and blob columns when possible and consider using the filesystem instead.
• Fixed length character fields are faster, at the expense of storage space. Disk space is cheap folks! However, smaller tables fit better in the buffer pool and reduce disk I/O.
• You cannot mix fixed and variable length (varchar, text, etc.) fields in MyISAM tables.
• Use the appropriate storage engine for the data you're storing/manipulating. For example, InnoDB tables are faster at FULLTEXT searches than MyISAM.
• Careful indexing pays big dividends.
• Columns in your WHERE, GROUP BY or ORDER BY clauses are good candidates for indexing, not the ones you SELECT.
• EXPLAIN is your friend. Learn it, understand it.
• Columns with a high cardinality in relation to the number of rows in the table are also good candidates for indexing.
• Whenever possible, use MySQL query functions to sort, filter and manipulate your data rather than using PHP (or whatever language you prefer).
• Although the MySQL optimizer will do this for you, I always try to code my queries to eliminate as many rows as possible as soon as possible.
• If you're running MySQL 5.0.37 or above, the MySQL Query Profiler is a cool new tool.
• Subqueries are powerful, but proceed with caution and consider rewriting slow subqueries as joins.
• The slow query log can help isolate problems you when your application seems sluggish, but enable it on a development server under a simulated load rather than on a live production server.
• I disagree with the assertion that enum types should be avoided. They are handy for mnemonic reasons and are actually stored internally as unsigned integers. A enum column with 256 or fewer members occupies only one byte of storage. However, they should be carefully thought through during the design phase to avoid changes later. Example, a five star rating system: enum('fail','poor','fair','good','best').
• Every RDBMS course hammers home data normalization. I agree with avoiding redundant data, but queries with multiple nested JOINs can bog down your application.
• Remember, denormalization involves flattening out your tables only after they are in 3NF. Also consider vertical or horizontal table partitioning.
• Writes (INSERT, UPDATE, REPLACE) normally have a higher priority than SELECTs. For tables (like hit counters) that involve frequent updates, consider using the DELAYED keyword to place those requests into a queue. Doing so allows the client to proceed before any write takes place. The downside, of course, to this is your reads are not necessarily real time.
• Of all the skills a Web developer must face, your DB and the backend are easily the most critical and complex. Taking the time to educate yourself is vital and well worth the effort, especially for large-scale applications.
• Finally, the MySQL online reference manual has a wealth of information on optimization. Spend some time with Optimizing SELECT and Other Statements.