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 .

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s