CODEWITHSUNDEEP
mysqlperformance
vlad
vlad

Reputation: 855

MySQL perfomance issues in SELECT

A simple database:

CREATE TABLE data (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50),
  value VARCHAR(10),
);

There are currently ~2 millions rows.

Query like:

SELECT value FROM data WHERE `code`='12345';

executes for 10-12 seconds.

What the best way to increase performance of simple select queries?

Upvotes: 0

Views: 94

Answers (4)

Joshua Martell
Joshua Martell

Reputation: 7212

You can use EXPLAIN SELECT ... to ask MySQL for information about how it's going to execute your query. This would tell you that it needs to check every row. Improving this is a matter of adding an index on the code column which is used in your WHERE clause.

EXPLAIN http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

CREATE INDEX http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Upvotes: 1

Neil
Neil

Reputation: 55392

In some cases you may find that adding an index on the code column does not suffice, so if that doesn't work for you you would need to add a (single) index for both the code and value columns.

Upvotes: 1

Sarke
Sarke

Reputation: 3235

Add index to code. Also, is code always numeric (make it type int) and/or is it unique (make it unique or primary key)?

Upvotes: 2

eggyal
eggyal

Reputation: 125865

Create an index on the code column:

ALTER TABLE data ADD INDEX (code)

Upvotes: 5

Related Questions