Hi. I've been testing some querys with this two versions of mysql and found weird results. First we try with 3.23.32:
(foo)-/usr/local/mysql_3.23.32/bin# ./mysql -uroot publish Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 3.23.32-log mysql> SELECT DISTINCT Article.ID as ArticleID FROM eZArticle_Article AS Article, eZArticle_ArticleCategoryLink as Link, eZArticle_ArticlePermission AS Permission, eZArticle_Category AS Category WHERE ( ( ( ( ( Permission.GroupID='-1' ) AND Permission.ReadPermission='1') ) AND ( Permission.GroupID='-1') AND Permission.ReadPermission='1' ) ) AND Article.IsPublished = 'true' AND Permission.ObjectID=Article.ID AND Link.ArticleID=Article.ID AND Category.ID=Link.CategoryID AND Category.ExcludeFromSearch = 'false' ORDER BY Article.Published DESC, Article.Puntos DESC LIMIT 220,10; +-----------+ | ArticleID | +-----------+ | 5665 | [...] | 7311 | +-----------+ 10 rows in set (54.19 sec) And then with 3.23.46: (foo)-/usr/local/mysql_3.23.46/bin# mysql -uroot publish Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.46-log mysql> SELECT DISTINCT Article.ID as ArticleID FROM eZArticle_Article AS Article, eZArticle_ArticleCategoryLink as Link, eZArticle_ArticlePermission AS Permission, eZArticle_Category AS Category WHERE ( ( ( ( ( Permission.GroupID='-1' ) AND Permission.ReadPermission='1') ) AND ( Permission.GroupID='-1') AND Permission.ReadPermission='1' ) ) AND Article.IsPublished = 'true' AND Permission.ObjectID=Article.ID AND Link.ArticleID=Article.ID AND Category.ID=Link.CategoryID AND Category.ExcludeFromSearch = 'false' ORDER BY Article.Published DESC, Article.Puntos DESC LIMIT 220,10; +-----------+ | ArticleID | +-----------+ | 5665 | [...] | 7311 | +-----------+ 10 rows in set (1 min 26.06 sec) Almost 30 seconds more with the same query!! We've found out that the main problem are the Permission checks in the WHERE clause, because taking them out makes the query execution down to 0.30 secs, but why the older version takes much less time? (I've tried this with 3.23.42 with the same results). Any way one of the tables has around 10000 elements and the other ones around 5000, isn't 1 minute too much time? Any hints to optmize the query? FYI it's running on a dedicated Dual 800Mhz, Linux server w/ 1Gb RAM. Thanks in advance. Cheers. -- Luis --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
