Technology

MySQL Performance Tuning

Only Ask for What You Need — And Be Explicit

Use

 SELECT name, rank, description FROM products

NOT

 SELECT * FROM sometable

Use Indexes to Speed Up Queries

CREATE TABLE albums (
    id        INTEGER      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title     VARCHAR(80)NOT NULL,

    INDEX title_idx (title)
);

Use Replace Queries

If the album with an id of 6 was supposed to have the title “Shaking the Tree,” you might write this query:

REPLACE INTO albums VALUES (6, ‘Shaking the Tree’)

It is important to understand how REPLACE determines whether or not a record is already in the table. MySQL will use any PRIMARY KEY or UNIQUE INDEX on the table to perform the check. If neither exist, the REPLACE effectively becomes an INSERT .