Join-fu: The Art of SQL Tuning for MySQL
Jay Pipes (MySQL)
10:00am Tuesday, 09/16/2008
Advanced, Best Practices, Performance Hall B
Join-fu is the art of performance-tuning your application’s SQL. Join Jay in a fun, irreverent look at the common ways application developers misuse and abuse their database.
We will cover a number of important topics in tuning your SQL application layer. We will not be covering tuning server variables or benchmarking, but instead the slides will be full of SQL and PHP code that the audience can immediately use to dramatically increase the performance of their schemas and applications.
The talk focuses on MySQL, but the tips and techniques used can easily be used to increase performance in a number of RDBMS-driven applications.
Photo of Jay Pipes
Jay Pipes
MySQL
Jay Pipes is the North American Community Relations Manager at MySQL. Co-author of Pro MySQL (Apress, 2005), Jay has also written articles for Linux Magazine and regularly assists software developers in identifying how to make the most effective use of MySQL. He has given sessions on performance tuning at the MySQL Users Conference, RedHat Summit, NY PHP Conference, OSCON, PHP-Quebec, ZendCond and Ohio LinuxFest, amongst others. He lives in Columbus, Ohio, with his wife, Julie, and his four animals. In his abundant free time, when not being pestered by his two needy cats and two noisy dogs, he daydreams in PHP code and ponders the ramifications of __clone().
vertical partitioning – split tables with many columns into multiple tables
horizontal partitioning – split table with many rows into multiple tables
vertical partitioning
- mixing frequently and infrequently accessed attributes in a single table
- space in buffer pool at a premium
- splitting table allows main records to consume the buffer pages without the extra data taking up space in memory
- need FULLTEXT on your text columns?
create a myisam indexing
-replication answer master both in db slave … do fulltext indexing on slave
the MYSQL query cache
- you must understand your application’s read/write patterns
-internal query cache design is a compromise between CPU usage and read performance
-stores the MYSQL_RESULT
http://jpipes.com/
