When a previously well-performing MySQL database suddenly slows to a crawl on simple queries like SELECT * FROM addresses with only 800 records, it's time for some serious debugging. Let me walk through the troubleshooting process I used to solve this exact issue.

First, let's verify the basic environment factors:

-- Check MySQL version
SHOW VARIABLES LIKE "%version%";

-- Check InnoDB status
SHOW ENGINE INNODB STATUS;

-- Check server resources
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

The EXPLAIN output showed a full table scan (type=ALL), which should still be fast for 800 rows. Let's investigate further:

-- Check if indexes are being used properly
ANALYZE TABLE addresses;

-- Look for table locks
SHOW OPEN TABLES WHERE In_use > 0;

-- Check for long-running transactions
SELECT * FROM information_schema.INNODB_TRX;

With 2GB RAM available but only 320MB used, MySQL might not be configured optimally:

-- Check key buffer settings
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';

-- Check current buffer usage
SHOW STATUS LIKE 'innodb_buffer_pool_read%';
SHOW STATUS LIKE 'innodb_buffer_pool_wait_free';

The symptom of rows appearing slowly suggests potential network or client issues:

-- Test with LIMIT to isolate the issue
SELECT * FROM addresses LIMIT 10;

-- Check if the issue persists when selecting fewer columns
SELECT address_id, name FROM addresses;

-- Test with mysql in batch mode
mysql -e "SELECT * FROM addresses" -u user -p database

Based on the findings, here are concrete steps to resolve the issue:

-- Optimize the table
OPTIMIZE TABLE addresses;

-- Increase InnoDB buffer pool (add to my.cnf)
SET GLOBAL innodb_buffer_pool_size=536870912; -- 512MB

-- Check for disk I/O issues
SHOW STATUS LIKE 'innodb%io%';

-- Consider adding appropriate indexes
ALTER TABLE addresses ADD INDEX (zip);
ALTER TABLE addresses ADD INDEX (city);

If the issue persists, consider these advanced troubleshooting steps:

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Check for disk fragmentation
SELECT TABLE_NAME, DATA_FREE 
FROM information_schema.TABLES 
WHERE DATA_FREE > 0;

-- Profile the query execution
SET profiling = 1;
SELECT * FROM addresses;
SHOW PROFILE;

---

After three years of stable operation, our MySQL 5.5 database running on a 2GB RAM virtual machine suddenly started exhibiting severe performance degradation with simple SELECT queries. A basic SELECT * FROM addresses query against an 800-row table that should return instantly now hangs indefinitely.

CREATE TABLE addresses (
  address_id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(64) CHARACTER SET latin1 NOT NULL,
  firstname varchar(64) CHARACTER SET latin1 NOT NULL,
  street varchar(64) CHARACTER SET latin1 NOT NULL,
  housenumber varchar(16) CHARACTER SET latin1 NOT NULL,
  zip varchar(5) CHARACTER SET latin1 NOT NULL,
  city varchar(64) CHARACTER SET latin1 NOT NULL,
  email varchar(64) CHARACTER SET latin1 NOT NULL,
  phone varchar(16) CHARACTER SET latin1 NOT NULL,
  birthdate date NOT NULL,
  PRIMARY KEY (address_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Key observations from our troubleshooting:

• EXPLAIN shows a simple full table scan (type: ALL)
• No visible resource constraints (CPU at 1-2%, 320MB RAM used)
• No blocking queries visible in mytop
• No recent hardware changes reported by IT

First, check for table corruption:

CHECK TABLE addresses;
REPAIR TABLE addresses;

Examine InnoDB status:

SHOW ENGINE INNODB STATUS;

Check for locking issues:

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

Character Set Conversion Overhead: The mix of latin1 columns in a utf8 table can cause runtime conversion costs. Consider:

ALTER TABLE addresses 
MODIFY name varchar(64) CHARACTER SET utf8 NOT NULL,
MODIFY firstname varchar(64) CHARACTER SET utf8 NOT NULL;

Network Buffer Issues: Add these to my.cnf:

[mysqld]
net_buffer_length=16K
max_allowed_packet=16M

Index Fragmentation: Rebuild the table:

ALTER TABLE addresses ENGINE=InnoDB;

Enable the slow query log with microsecond precision:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 0;

Profile the problematic query:

SET profiling = 1;
SELECT * FROM addresses;
SHOW PROFILE;
SHOW PROFILE CPU FOR QUERY 1;