Forums / Developer / Whats wrong with my ExtendedAttributeFilter performance.
Jan Komárek
Wednesday 18 March 2009 6:51:19 am
Hello,
I have a extended attribute filter:
<?php class titleAndDescriptionFilter { /*! Constructor */ function titleAndDescriptionFilter() { // Empty... } function createSqlParts( $params ) { if (isset($params[0]) && trim($params[0]) != '') { $sqlTables= ',ezcontentobject_attribute AS filterName ,ezcontentobject_attribute AS filterText'; $sqlJoins = ' filterName.contentobject_id = ezcontentobject.id AND filterName.version = ezcontentobject.current_version AND filterName.contentclassattribute_id = 323 AND filterText.contentobject_id = ezcontentobject.id AND filterText.version = ezcontentobject.current_version AND filterText.contentclassattribute_id = 324 AND'; $sqlJoins .= " (LOWER(filterName.data_text) LIKE LOWER('%".$params[0]."%') OR "; $sqlJoins .= "LOWER(filterText.data_text) LIKE LOWER('%".$params[0]."%')) AND "; } return array( 'tables' => $sqlTables, 'joins' => $sqlCond ); } }
But I have a big performance problem. If I run fetch with thid filter my MySQL is fulloaded by minutes. After 5minutes of 100% load I kill thread. But if I try SQL in cli client, there is no problem and result of query is served in 0.1 second and faster.
EZ Publish version is 4.0
I have no idea what is wrong. Thanks for help.
André R.
Wednesday 18 March 2009 7:26:52 am
You are using several columns that do not have index, especially data_text will cause issues, so you should look into using sort_key_string instead (note: sort_key_string is already lowercase so you can have the lower case stuff in php for param instead and remove the one for the column).
eZ Online Editor 5: http://projects.ez.no/ezoe || eZJSCore (Ajax): http://projects.ez.no/ezjscore || eZ Publish EE http://ez.no/eZPublish/eZ-Publish-Enterprise-Subscription @: http://twitter.com/andrerom
Wednesday 18 March 2009 8:36:01 am
$sqlJoins = ' filterName.contentobject_id = ezcontentobject.id AND filterName.version = ezcontentobject.current_version AND filterName.contentclassattribute_id = 323 AND filterText.contentobject_id = ezcontentobject.id AND filterText.version = ezcontentobject.current_version AND filterText.contentclassattribute_id = 324 AND'; $sqlJoins .= " (filterName.sort_key_string LIKE '%".strtolower($params[0])."%' OR "; $sqlJoins .= "filterText.sort_key_string LIKE '%".strtolower($params[0])."%') AND ";
It looks like better. But still incredible slow. Which other columns are without index. I didnt find anyone.
Friday 20 March 2009 7:06:36 pm
This problem is not about SQL. I mean if i looka at the SQL query which is build without ExtendedAttributeFilter and add joins and conditions manualy.
Query is realy fast. It only 3.5MB data in database.
Saturday 21 March 2009 4:46:46 am
Ok, so some issue with your mysql setup? found the cause yet?
Stéphane Couzinier
Sunday 22 March 2009 3:11:29 pm
Hi
For good performance, 'like' should not be use like this. the % at the begin of the condition prevent mysql to use correctly index.FYI it's the same when you use LOWER on an index column.
Just to test, remove the first %. it should be faster. but not the same result... Using OR will also slow down the query.
http://www.kouz-cooking.fr
Tuesday 24 March 2009 5:11:18 am
My big mistake. I create join conditions and store them in $sqlJoins varible. But when returning params i use variable $sqlCond which not exists! So I make two cross joins over attribute table with like operators and its database killer....
But its guide me to another question. When I want to use INNER JOIN or LEFT JOIN in FROM clause its imposible to use columns, which is not in last mentioned table.
Its described better here http://mattiasgeniar.be/2008/10/30/mysql-1054-unknown-column-tablecolumnname-in-on-clause-even-though-column-name-exists/
So I think, there could be brackets added by default when extended attribute filter is used.