mysql usage is terrible, how to improve?

[yacenty]

Hi guys, today my ISP told me that if I don't change the gallery account will be closed because of huge mysql usage.
as I was told, I don't use indexes and there is a lot of queries that last over 3sec.
my gallery www.agrofoto.pl has 6500pictures, every day there is 1500visits and about 60.000 page views.
how can I improve mysql? it's nearly impossible to find better ISP in Poland than my current one is.
What should I do?

Thanks in advance
BR

YacentY

[Hein Traag]

Why not go to a provider outside of Poland ? There are lots of internet space providers with good prices and good results. Look around and you will find several who also support coppermine installations.

Cheers!
Hein

[yacenty]

because of pings, and quality of service, till today I always told that home.pl is best provider in my sourounding (central europe), but today I am totally dissappointed with their behaviour?

any suggestions?

BR
YacentY

[Hein Traag]

Searching with the keyword "providers" gives you this page: http://forum.coppermine-gallery.net/index.php?topic=19645.0 which lists quite a lot of hosts which support CPG.

If you want to stay with your current provider and want advice on improving your MySql then maybe somebody with more knowledge on MySql can step in and offer advice.

Cheers!
Hein

[Joachim Müller]

Make sure that you have detailed stats turned off in coppermine's config (huge resource eaters).
You have way too many details (that burn queries) on your page, reduce them. Turn off the online stats, and only display the first level of categories and no albums on your index page. This will reduce the number of queries dramatically.

[yacenty]

what do You mean no albums on index page?
I have no stats, it was disabled at the begining because of fast growing of database size,
what kind of details are talking about?

best regards,
YacentY

[Hein Traag]

0n the url http://www.agrofoto.pl/gallery/ you have a lot of albums on the frontpage of the gallery. This eats up speed as wel. Try moving them into a categorie. Also your album and catlist are set to 2 or more rows, try trimming that down to 1 or none at all. Users wil click on anyway. Less albums posted on your frontpage, either because they are not in a category or alblist and catlist are set to 2 or more rows, means less heavy work for the MySql.

Cheers!
Hein

[yacenty]

ok, now it's reduced, I hope it will be enough for my ISP.
Thanks for remarks,

BR
YacentY

[yacenty]

sorry for responding for my post but I would like to make this topic visible
what is the reason to put 'and aid in' in this querry?
SELECT COUNT(*) from cpg143_pictures WHERE ((aid='41' ) OR (keywords like '%Ci�gnik tractor JD John Deree%' )) AND approved='YES' AND aid IN (6,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,130,135,136,137,140,141,142,144,146,147,149,151,152,153,154,155);

I hope this AND aid IN (... make this querry more complex and for me make no special sense to use it, it only make longer time for this querry.
What do You think? is this AND aid IN necessary?

best regards,
YacentY

[Nibbler]

It is necessary if you want to use the album keywords feature. If you don't want it then stop using the feature.

[yacenty]

where can I turn it off? sorry but I don't see setting for this feature. Or should I do it via replacing php code or by deleting some properties of categories, albums, etc.?

BR,
YacentY

[Joachim Müller]

Enable clickable keywords in search. Don't turn options on in the first place if you don't understand what they do. Please read the docs before asking questions.

[yacenty]

I've used it when my gallery was small and it was working very fine, I didn't know that this option may slow my gallery so much.

BR
YacentY