Hello,
I saw that the table Myprefix_document_category has 3 fields, and only the first one (id) has an index.
It appears that adding an index on document_id and category_id speed the queries by factor 100 !
Before :
# Query_time: 0.143114 Lock_time: 0.000072 Rows_sent: 1 Rows_examined: 342810
use stage3;
SET timestamp=1350912013;
SELECT COUNT(*) FROM extra_edocman_documents AS a WHERE ((a.user_ids = '' AND a.access IN (1,1,2,3,4,5,6)) OR a.user_ids='102' OR a.user_ids LIKE '102,%' OR a.user_ids LIKE '%,102,%' OR a.user_ids LIKE '%,102') AND a.id IN (SELECT document_id FROM extra_edocman_document_category WHERE category_id = 3 );
After :
# Query_time: 0.003533 Lock_time: 0.000062 Rows_sent: 1 Rows_examined: 590
SET timestamp=1350912011;
SELECT COUNT(*) FROM extra_edocman_documents AS a WHERE ((a.user_ids = '' AND a.access IN (1,1,2,3,4,5,6)) OR a.user_ids='102' OR a.user_ids LIKE '102,%' OR a.user_ids LIKE '%,102,%' OR a.user_ids LIKE '%,102') AND a.id IN (SELECT document_id FROM extra_edocman_document_category WHERE category_id = 3 );
It seems nothing like this as the query seems quite fast, but if you have for instance 10 categories(wich is not big), that will speed up your site by 1 second !
Thanks for adding this in a next release !
ALTER TABLE `Myprefix_edocman_document_category` ADD INDEX ( `document_id` )
ALTER TABLE `Myprefix_edocman_document_category` ADD INDEX ( `category_id` )
