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
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
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
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 currently running MySQL connections with the SHOW FULL PROCESSLIST command, you find the following details: mysql> SHOW FULL PROCESSLIST\G … *************************** 6. […]
Read More
Learn from Oracle experts about the myths and realities of what causes poor Oracle database performance and tips for improving it.
Read More
The nonrecursive form of a CTE can be used as an alternative to derived tables and views. Learn how.
Read More
Apply SQL Server derived tables when the use of column aliases is not possible because another clause is processed by the SQL translator before the alias name is known. Learn more.
Read More
Writing a SQL Server SELECT statement using the join operator is can help the Database Engine to find a more efficient strategy for retrieving the appropriate data. Learn how.
Read More
In SQL Server, sometimes it is necessary to retrieve, in addition to the matching rows, the unmatched rows from one or both of the tables. Such an operation is called an outer join.
Read More
The SQL Server IDENTITY property lets you specify a counter of values for a specific column of a table. You can use it to let the system generate unique numeric values for the table column of your choice. Learn more.
Read More
A NULL in the CREATE TABLE statement specifies that a special value called NULL is allowed in the column. Learn how these values differ from all other values in a SQL Server database.
Read More
Query processing is performed by the Relational Engine in SQL Server. SQL Server takes four steps to process a query: parsing, algebrizing, optimizing, and execution.
Read More