Hi all,
I have a query in my program which is taking to long when I get data in the
database. I'm currently testing with ~40.000 articles in the system.
The query which I'm using is (takes about 2 secs ):
SELECT 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 Article.IsPublished = 'true'
AND Link.CategoryID='1'
AND Permission.ObjectID=Article.ID
AND Link.ArticleID=Article.ID
AND Category.ID=Link.CategoryID
ORDER BY Article.Published DESC
LIMIT 10,10
I've tested with indexing and table optimizing:
alter table eZArticle_Article add index ( ID );
alter table eZArticle_Article add index ( Name );
alter table eZArticle_ArticlePermission add index ( ObjectID );
alter table eZArticle_ArticleCategoryLink add index ( ArticleID );
alter table eZArticle_ArticleCategoryLink add index ( CategoryID );
alter table eZArticle_ArticlePermission add index ( ReadPermission );
alter table eZArticle_ArticlePermission add index ( WritePermission );
alter table eZArticle_Article add index ( Published );
optimize table eZArticle_Article;
optimize table eZArticle_ArticlePermission;
optimize table eZArticle_ArticleCategoryLink;
optimize table eZArticle_Category;
Is it possible to optimize this query further? Or is this the kind of speed I
would get from this kind of query.
Test machine: Dual PIII 800 with SCSI hardware RAID and 512 MB ram.
--
Bård Farstad
Systems developer
ez.no | developer.ez.no | zez.org
---------------------------------------------------------------------
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
