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).
$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.
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.
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.
You must be logged in to post messages in this topic!