Here are some suggestions for performance enhancements that I feel should be included in your code. With a heavily loaded myAlbum-P install we were experiencing some very high database loads. Having looked into the cause of the load some slow queries showed themselves to be the culprits.

In index.php, viewcat.php and include/search.inc.php there are queries which access the xoops_myalbum_photos table joined with some other tables. These queries "ORDER BY" the date field which is not indexed. If you run these queries against a large dataset the query time is far too high.

I added an index on the date field but mysql still insisted on using the index on the status column, so I added "USE INDEX(date)" after the photos table. This gave a dramatic performance improvement.

On a heavily loaded database server (Dual 3GHz Xeons with 8Gb RAM) the query execution time dropped from between 8 and 27 seconds to between 0.02 and 0.1 seconds.

I hope you will make these simple changes.

Regards,

Nick

hi Nick.

Your post sounds quite interesting.

`date` field, ok.
It's just a missing.
I'll add the index.

Quote:In my evironment(MySQL 3.23.58), just adding the index for date raise the query speed enough.
Adding 'USE INDEX (`date`)' after myalbum_photos on FROM section changes nothing.

Which version of MySQL do you use?

Anyway I'll modified SQLs.
Thanks a lot!

Hi GIJOE,

Thanks for your reply. Our production server is running MySQL 4.0.26 but I have tested the queries on 4.1 and 5.0. For some reason on 4.0 and 4.1 the optimizer uses the status index which is next to useless due to the low cardinality. If you include the "USE INDEX(date)" in the queries it will have no adverse affects on the servers which get it correct.

If you run an EXPLAIN on both versions of the query you will see what I mean.

Thanks for including the changes.

hi Nick.

Quote:In the online reference of MySQL:
"USE INDEX" does not affect the speed about ORDER section.
But I believe you because you've checked the performance experimentally.

Quote:I've already checked.
But explain just returns "Using filesort ..." in any conditions
It can be the version specific problem.

I've just added your code (modify sql/myalbum*.sql, index.php, viewcat.php).
It will be released later.

Hi GIJOE,

I will run some checks on an old 3.23 install when I get a chance. The whole point in using the date index is to remove the need for the filesort. With a large number of photos ( > 100,000 ) it is the filesort that performs particularly slowly.