Thursday, October 3, 2024

Better Indexes $ave You Money

Can database performance improvements be achieved with zero code changes? Learn how to use one simple advanced technique to make better MySQL indexes and improve your queries by 500% or more. Even with a highly indexed schema as shown in our 10 table join example, significant improvements in performance can be achieved.

This presentation introduces the approach for correct identification and verification of problem SQL statements and then describes the means of identifying index choices for optimization. Then discussed is not only how to apply indexes to improve query performance, but how to apply better indexes and provide even great performance gains.

This presentation includes:

  • 6 steps to successful SQL review
  • Basic ways via application logging and TCP/IP analysis to capture SQL
  • All the commands necessary to identify why and how to create indexes
  • How the number of table rows and different storage engines can effect query performance
  • How to create an iterative verification process

Adding indexes is not the only approach nor the best approach to query optimization however learning how MySQL indexes work can provide quick improvements without the need to deploy code changes.

Presenter: Ronald Bradford
Schedule: Tuesday, March 22, 2011 in New York

UNSIGNED

This is an optional attribute for a column definition of a numeric data type. By default, all numeric columns store SIGNED values, the use of UNSIGNED can increase the range when only positive INTEGER values are needed.

The following table shows the maximum range of values for SIGNED and UNSIGNED numeric data types.

Datatype SIGNED UNSIGNED
TINYINT -128 to 127 0 to 255
SMALLINT -32768 to 32767 0 to 65535
MEDIUMINT -8388608 to 8388607 0 to 16777215
INT -2147483648 to 2147483647 0 to 4294967295
BIGINT -9223372036854775808 to 9223372036854775807 0 to 18446744073709551615

For more information see the MySQL Reference Manual Numeric Types.