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?
Best Answer
Your problem is quite straightforward.
SELECT * FROM web.files order by file_name_eng desc
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 convertweb.files
to InnoDB withI 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
From the EXPLAIN plan, I can see that the query
WHERE
clausefile_name_eng
column, so there is no assistance in order the table (a filesort would happen, but happen fast)Therefore, you should run
Rhetorial Questions
SELECT *
?