book

Professional Website Performance: Optimizing the Front-End and Back-End

by Peter Smith

November 2012

Intermediate to advanced

480 pages

14h 6m

English

Wrox

Read now

Unlock full access

A Brief History of Web BrowsersInside HTTPHow Browsers Download and Render ContentSummary
Understanding the Types of CachingControlling CachingDealing with Intermediate CachesCaching HTTP ResponsesDNS Caching and PrefetchingSummary
Who Uses CompressionUnderstanding How Compression WorksCompression in PHPSummary
JavaScript MinificationCSS MinificationHTML MinificationSummary
Understanding Image FormatsOptimizing ImagesCSS SpritesCSS PerformanceLooking ForwardSummary
JavaScript, JScript, and ECMAScriptThe Document Object ModelGetting the Most from JavaScriptAjaxSummary

ApacheLooking Beyond ApacheMultiserver Setups with Nginx and ApacheLoad BalancersSummary
Looking Inside MySQLUnderstanding the Storage EnginesTuning MySQLTuning MyISAMTuning InnoDBWorking with the Query CacheOptimizing SQLSummary
Using ReplicationPartitioningShardingComplementing MySQLAlternatives to MySQLSummary
NoSQL FlavorsmemcacheMongoDBOther NoSQL TechnologiesSummary
SSL CachingSSL Termination and EndpointsSending Intermediate CertificatesDetermining Key SizesSelecting Cipher SuitesInvesting in Hardware AccelerationThe Future of SSLSummary
Extensions and CompilingOpcode CachingCompiling PHPSessionsEfficient PHP ProgrammingProfiling with xhprofSummary
The Three-Way HandshakeTCP Performance
Understanding Mobile PlatformsResponsive ContentJavaScript and CSS CompatibilityCaching in Mobile Devices
The LZW FamilyHuffman EncodingCompression Implementations

Content preview from Professional Website Performance: Optimizing the Front-End and Back-End

Chapter 8 Tuning MySQL

WHAT’S IN THIS CHAPTER?

Getting to know the myriad of performance tuning options available in MySQL
Writing efficient SQL queries, and spotting bottlenecks in existing queries
Getting the most out of the MySQL query cache
Discovering the latest advances in MyISAM and InnoDB

As a relative newcomer on the scene (with the first release in 1995), MySQL has quickly become the de facto relational database management system (RDBMS) for Linux, and one of the most popular for Windows and UNIX. Despite lacking some of the more advanced features found in other RDBMSs (this is gradually changing, but things like stored procedures and transactions are still relatively new additions), MySQL is lightweight, and has a good reputation for speed, making it an excellent choice for general-purpose web development.

Despite this reputation for speed, there is typically still a huge scope for optimization, both in the configuration of the MySQL server, and in queries themselves. This chapter familiarizes you with the performance aspects of MySQL.

This chapter begins by examining how the main storage engines differ, and the impact that this has on performance. After looking at general MySQL tuning issues, this chapter discusses tuning of the two most popular engines: MyISAM and InnoDB. After a discussion of MySQL’s query caching capabilities, this chapter concludes with tips on how to write more efficient SQL queries, and how to debug slow-running queries.

NOTE The terms cache ...