Most Popular Query Performance

Optimize MySQL Query Performance with Explicitly Named Fields

It’s common to see queries like these: SELECT (*) FROM airport; SELECT COUNT(*) FROM airport; These queries use the asterisk (*) wildcard for convenience. However, this convenience comes at a price: The * wildcard forces MySQL to read every field or record in the table, adding to the overall query processing time. To avoid this, explicitly name the output fields you […]

Read More

Optimize MySQL Performance with Session Variables and Temporary Tables

Session-based server variables can also come in handy if you want to avoid nesting queries within each other. Therefore, while the following query will list all flights where the current price is above average: SELECT FlightID FROM stats   WHERE CurrPrice >   (SELECT AVG(CurrPrice) FROM stats); you can accomplish the same thing by splitting the task into two queries and using a server-side MySQL […]

Read More

Optimize MySQL Query Performance Using Joins Instead of Subqueries

Although MySQL comes with built-in intelligence to automatically optimize joins and subqueries, this optimization is far from perfect. An experienced database architect can often improve query performance by orders of magnitude through simple tweaks to the way queries are written. For example, MySQL is better at optimizing joins than subqueries, so if you find the […]

Read More

Identifying MySQL Performance Problems

Users report that your application is too slow. After determining there is no physical system resource bottleneck, you turn your attention to the MySQL database. Finding a Slow SQL Statement Looking at the current running MySQL connections with the SHOW FULL PROCESSLIST command, you find the following details: mysql> SHOW FULL PROCESSLIST\G … *************************** 6. […]

Read More