This site has been archived and you can no longer log in or post new messages. For up-to-date community resources please visit ezplatform.com

eZ Community » Forums » Install & configuration » MySQL problems in Windows
expandshrink

MySQL problems in Windows

MySQL problems in Windows

Tuesday 23 June 2009 11:30:28 pm - 2 replies

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?

Wednesday 24 June 2009 10: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

Wednesday 24 June 2009 5:52:37 pm

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.

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from