MySQL problems in Windows

Tuesday 23 June 2009 2:30:28 pm

I'm having some very slow queries on a windows setup. It's especially one query that's slow, and unfortunately it's one of the very important ones that's used all over in eZ Publish:

SELECT id, contentobject_id, version, language_code, language_id, contentclassattribute_id, attribute_original_id, sort_key_int, sort_key_string, data_type_string, data_text, data_int, data_float FROM ezcontentobject_attribute WHERE contentclassattribute_id='232' AND contentobject_id='5128' AND version='30' order by id desc;
(applies to all ID's)

The query takes more than 2 seconds each time. All indexes in the database is set correctly.
If I try the query in the mysql console it's just as slow, but everything speeds up if I remove the "order by id desc" part of the query. It's also very fast if I try to sort on another field in the table.

The server is a very powerful server, but I'm not that experienced on configuring Windows so it might be related to this. I've tried to change the "normal" settings like key_buffer_size, sort_buffer_size and so on, but nothing helps.

The table has about 800.000 records.

Any Windows gurus with any suggestions?

Jo Henrik Endrerud | System Developer @ Seeds Consulting | http://www.seeds.no

Wednesday 24 June 2009 8:52:37 am

Hi. Explain on the query is as follows:

explain SELECT id, contentobject_id, version, language_code, language_id, contentclassattribute_id, attribute_original_id, sort_key_int, sort_key_string, data_type_string, data_text, data_int, data_float FROM ezcontentobject_attribute WHERE contentclassattribute_id='232' AND contentobject_id='5128' AND version='30' order by id desc;
+----+-------------+---------------------------+-------+------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------+-------+------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | ezcontentobject_attribute | index | ezcontentobject_attribute_co_id_ver_lang_code,ezcontentobject_attribute_contentobject_id | PRIMARY | 8 | NULL | 1016061 | Using where |
+----+-------------+---------------------------+-------+------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Do you have any suggestions based on this?

I won't mind trying out the mysqltuner if thats the only option i have.

Jo Henrik Endrerud | System Developer @ Seeds Consulting | http://www.seeds.no