Large Db And Boost Performance

Hello,

I have a very large installation: 842068 pictures online. Table ms_photos have 841210 entry and ms_blog_photos : 839202. ms_photos_keywords_connect have too entry but no problem with this table.

Since some time Sytist become slow, I have make tuning on MariaDB but at this time I can't do more. MySqlTuner always say me there is joins without index. MySQL process take CPU 100%, so I have search the problem.

Query who take long time :
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_photos.pic_id=ms_blog_photos.bp_pic WHERE bp_product='8' ORDER BY bp_order ASC
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_photos.pic_id=ms_blog_photos.bp_pic WHERE bp_product='37' ORDER BY bp_order ASC
SELECT * FROM ms_photos WHERE pic_key='a2a74fa4ccfca21a88d6b7f96782d620'
SELECT *,date_format(pic_date, '%d/%m/%Y ') AS pic_date,date_format(pic_date_taken, '%d/%m/%Y ') AS pic_date_taken FROM ms_photos WHERE pic_key='d1f83242b962e4595eadad5cd3af5602'

So i see i need index on ms_photos or ms_blog_photos, I have add 3 index :
ALTER TABLE `ms_photos` ADD INDEX(`pic_key`);
ALTER TABLE `ms_photos` ADD INDEX(`pic_id`);
ALTER TABLE `ms_blog_photos` ADD INDEX(`bp_product`); #(

Edited by

These are the indexes that should be on the ms_blog_photos table. If they are not already there for the table in the database, you can add them through PHPMyAdmin from your hosting control panel.

Edited by

Thank you, effectively my DB don't have all indexes.

Do you have a script or just listing of indexes normally present in the DB. I have an old stylist installation and I think there is perhaps database evolution and I don't have indexes up to date.

Edited by

I don't have a list of indexes. But you should just compare that screenshot to yours. And this is the ms_photos table.

Edited by