You may find what I am about to suggest a little surprising
Add the following indexes
ALTER TABLE mytable ADD INDEX id_value_ndx (id,value);
ALTER TABLE mytable ADD INDEX value_id_ndx (value,id);
This will actually cache id and value together in the MyISAM Key Cache. That way, all queries are in memory only. Queries will not go to the table at all.
I would also propose you make a dedicated 1GB MyISAM Key Cache just for your table
STEP 01: Create a script to load the table into the dedicated key cache
cd /var/lib/mysql
echo "SET GLOBAL mykeycache.key_buffer_size = 1024 * 1024 * 1024;" > init-file.sql
echo "CACHE INDEX mytable IN mykeycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE mykeycache; >> init-file.sql
STEP 02 : Add this to /etc/my.cnf
[mysqld]
init-file=/var/lib/mysql/init-file/sql
STEP 03 : service mysql restart
From here on, every restart of mysql will setup this private key cache for your table
Give it a Try !!!
The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.
I wrote about pros and cons of using MyISAM vs InnoDB
With regard to your current question, here is a possible scenario:
article and article_comments are both MyISAM tablesarticle_comments has one or more indexes with status as a column- Index page updates for
article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
- You have SELECT queries that perform JOINs between
article and article_comments
In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)
Best Answer
Your problem is not the DB Engine, but the lack of the indices. Indices make possible for your database to find things (records) much faster. You had to make some.
I suggest to read an sql indexing tutorial (google is your friend), and then come back with a new question, if you have yet one.
Extension after you gave your query: in this case there is two problem. First,
LIMITisn't really simple to make faster with indices. But it is not a big problem, because in your query is it visible, what is the cause of the sloooow query. It is the multiple inequality conditions in the WHERE. I didn't give a medal for the liferay programmers for this sh... sometimes-maybe-not-really-useful solution.The greatest problem is, that such indices can't be make faster by indices easily. On big tables they should be avoided, if we want to develop high-quality software.
The solution - indexing a table for multiple inequality search term - isn't known currently also for me, thus I opened a new question for you, what can be done in MySQL in your problem. Maybe we get some interesting answer and in this case I will be able to extend also this question.