If you have a support question or comment, click the Post New Topic link below.
Current Version: 3.4.5Sytist Manual | Sytist Articles 

Large Db And Boost Performance

 
Please log in or Create an account to post or reply to topics.
 
Yann Queniart
108 posts
Wed Feb 27, 19
11:02 AM
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`); #(
Tim - PicturesPro.com
11185 posts
admin
Thu Feb 28, 19
3:51 AM
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.
Attached Photos

 
 
 
 
 
Tim Grissett, DIA - PicturesPro.com || My Email Address: info@picturespro.com
Yann Queniart
108 posts
Thu Feb 28, 19
3:56 AM
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.
Tim - PicturesPro.com
11185 posts
admin
Thu Feb 28, 19
4:23 AM
I don't have a list of indexes. But you should just compare that screenshot to yours. And this is the ms_photos table.
Attached Photos

 
 
 
 
 
Tim Grissett, DIA - PicturesPro.com || My Email Address: info@picturespro.com
Loading more pages
Loading more pages

Sign up for email promotions.

Your information is safe with us and won't be shared.

Thank you for signing up!

 
©2003 - 2019 Grissett, LLC. All Rights Reserved.

By continuing to browse or by clicking Accept Cookies, you agree to the storing of cookies on your device necessary to provide you with the services available through our website.

    Accept   Privacy & Cookie Policy
Loading More Photos
Scroll To Top
Close Window
Loading
Close