Whats wrong with my ExtendedAttributeFilter performance.

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.

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.

Saturday 21 March 2009 4:46:46 am

Ok, so some issue with your mysql setup? found the cause yet?

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

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.