Whats wrong with my ExtendedAttributeFilter performance.

Next topic

Author	Message
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
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.