MySQL problems in Windows

Author Message

Jo Henrik Endrerud

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

Gaetano Giunta

Wednesday 24 June 2009 1:18:46 am

Could you send an explain_plan of that query?

If installing perl+[cygwin or unxutils] is ok with you, I can send a patched version of the mysqltuner.pl perl script that works on windows and can easily give you some tips about mysql configuration

Principal Consultant International Business
Member of the Community Project Board

Jo Henrik Endrerud

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

Powered by eZ Publish™ CMS Open Source Web Content Management. Copyright © 1999-2014 eZ Systems AS (except where otherwise noted). All rights reserved.