A simple MySQL query takes 2 to 3 seconds?

Question

I have a fairly simple process that periodically pulls RSS feeds and updates articles in a MySQL database.

The table of articles is filled up to about 130 thousand lines right now. For each article found, the processor checks if the article exists. These requests almost always take 300 milliseconds, and approximately every 10 or 20 attempts, they take more than 2 seconds.

SELECT id FROM `articles` WHERE (guid = 'http://example.com/feed.rss')  LIMIT 1;
# Query_time: 2.754567  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0

I have an index in the guid column, but whenever a new article is found, it is added to the article table - the request cache is invalid (right?).

Some of the other fields in the slow query log log 120+ lines.

Of course, on my development machine, these queries take about 0.2 milliseconds.

The server is a virtual host from Engine Yard Solo (EC2) with 1.7 GB of memory and any EC2 CPU delivered these days.

Any advice is appreciated.

Update

As it turns out, the problem was between the chair and the keyboard.

I had an index on "id", but it requested "guid".

Adding an index to "guid" reduced the query time to 0.2 ms.

Thanks for all the helpful tips to everyone!

Answer 1

Run:

EXPLAIN SELECT id FROM `articles` WHERE (guid = 'http://example.com/feed.rss')  LIMIT 1;

EXPLAIN . , MySQL. , - 2,7 , / . 0, , MySQL , , , , , , , , .

, , - articles, , , .

Answer 2

, , , , , . EC2 IO/s, , MySQL , .

, (, my.cnf key_buffer (MyISAM) innodb_buffer_pool_size (InnoDB)),

SELECT guid FROM articles

EXPLAIN, , " ". , :

SELECT guid FROM articles FORCE INDEX (guid) WHERE LENGTH(guid) > 0

, guid PRIMARY KEY UNIQUE, , . guid_crc32 INT UNSIGNED CRC32 guid

ALTER TABLE articles ADD COLUMN guid_crc32 INT UNSIGNED, ADD INDEX guid_crc32 (guid_crc32);
UPDATE articles SET guid_crc32 = CRC32(guid);

SELECT :

SELECT id FROM articles WHERE guid = 'http://example.com/feed.rss' AND guid_crc32 = CRC32('http://example.com/feed.rss') LIMIT 1;

guid_crc32, , , guid.

Answer 3

, mysql . "CHECK TABLE articles", , .

, EXPLAIN dev prod . , OPTIMIZE TABLE.

myisam innodb?

Answer 4

, GUID , - , - "". . , , .

// "".

SQL :

show table status like 'articles';
explain SELECT id FROM `articles` WHERE (guid = 'http://example.com/feed.rss')  LIMIT 1;
explain articles;

, ( Linux):

iostat 5 5

, , 1.7mb , - .

, SQL- my.cnf?