MySQL not responding, Writing to net status I just had to reboot MySQL to regain access to some of my website. It had under 300MB of RAM used. Another product was working fine though, accessing it's own tables which are InnoDB.

Here's the show processlist before I rebooted it:

3629079 user    ip:4815 web Query   2324    Writing to net  SELECT * FROM web.files order by file_name_eng desc
3632228 user    ip:4817 null    Sleep   2323        null
3632453 user    ip:4818 null    Sleep   2323        null
3649490 user2   127.0.0.1:28050 null    Query   1967    Locked  UPDATE web.files SET date_publish_test = NOW(), uuser_id = 2 WHERE file_id = 20454


That is a simple select, here's the explain of it (35K records in total, type MyISAM):

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  files   ALL null    null    null    null    30593   Using filesort


Version: 5.0.54a-enterprise-gpl-nt on Windows 2003.

Am I going at this properly?
Your problem is quite straightforward.


You mentioned that the table is using the MyISAM storage engine.
The processlist shows


Process ID 3629079 is running SELECT * FROM web.files order by file_name_eng desc
SELECT has been running 2324 seconds (39 min 44 sec)
Process ID 3649490 is running UPDATE query
SELECT has blocking the UPDATE for 1967 sec (32 min 47 sec)



NOTE : Any INSERT, UPDATE, DELETE on a MyISAM table requires a full table lock each time.

NOTE : If a series of 50 UPDATEs is issued on one table, each UPDATE requires a full table lock.

SUGGESTION #1

If web.files has no FULLTEXT indexes, you should convert web.files to InnoDB with

ALTER TABLE web.files ENGINE=InnoDB;


I suggested this because InnoDB will allow UPDATEs, DELETEs, and INSERTs to harmoniously coexist with SELECTs via MVCC and Transaction Isolation.

If you are not the Project Manager/Lead Developer, please consult with your Project Manager/Lead Developer before doing this.

SUGGESTION #2

SELECT * FROM web.files order by file_name_eng desc


From the EXPLAIN plan, I can see that the query


Query must do a full table scan because there is no WHERE clause
There is no index on file_name_eng column, so there is no assistance in order the table (a filesort would happen, but happen fast)


Therefore, you should run

ALTER TABLE web.files ADD INDEX (file_name_eng);


Rhetorial Questions


Do you need SELECT * ?
Could you list only the columns you really need ?