Whats wrong with my ExtendedAttributeFilter performance.

Whats wrong with my ExtendedAttributeFilter performance.

Wednesday 18 March 2009 6:51:19 am - 6 replies

Modified on Wednesday 18 March 2009 6:57:07 am by Jan Komárek

Author Message

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

Jan Komárek

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.

Jan Komárek

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.

André R.

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

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

Jan Komárek

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.

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

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