Joomla mySQL database needs optimisation and I have no idea what to do?

Hi there,
I was recently told that our joomla database needs to be optimized.
I have no idea how to do this.

This was the info the server gave me to deal with and I have a backup of the database in an sql format and I don't know what to do next.

Can someone help me do this optimization?


DataBase C244045_joom2 chmoded to zero

Time: 50% (12264 sec) Amount: 51% (1177 queries) Rows Examined/Sent: 32757 / 1724 Avg. Query Exec/Lock Time: 10 / 1 sec.
use C244045_joom2; SELECT t0.*, b.zone, b.approved, campaign_id, banner_id, FLOOR(RAND() * relative_weighting) as rw FROM jos_ad_agency_campaign_banner LEFT OUTER JOIN jos_ad_agency_banners as b ON b.id=banner_id LEFT JOIN jos_ad_agency_campaign as t0 ON campaign_id=t0.id WHERE b.zone='58' AND b.approved='Y' AND ((t0.approved='Y') AND (t0.approved='Y' AND ('2010-03-30 03:59:53' > t0.start_date) AND ((t0.type IN ('cpm','pc') AND t0.quantity>0) OR (t0.type='fr' AND NOW() < t0.validity)))) AND t0.status='1' AND banner_id NOT IN (0) AND (b.media_type = 'Standard' OR b.media_type = 'TextLink' OR b.media_type = 'Flash' OR b.media_type = ' ... [too long]

Time: 5% (1336 sec) Amount: 5% (125 queries) Rows Examined/Sent: 127287 / 121 Avg. Query Exec/Lock Time: 10 / 1 sec.
SELECT how_many FROM jos_ad_agency_stat WHERE substring(entry_date,1,10)='2010-03-30' and ip_address=1993443755 AND campaign_id=6 AND banner_id=17 AND `type`='impressions' limit 1;

Time: 5% (1330 sec) Amount: 4% (114 queries) Rows Examined/Sent: 0 / 111 Avg. Query Exec/Lock Time: 11 / 3 sec.
use C244045_joom2; SELECT * FROM jos_session WHERE session_id = 'd8qa0mmfojrma4vam1gm5m7vn6';

Time: 4% (1137 sec) Amount: 4% (105 queries) Rows Examined/Sent: 0 / 0 Avg. Query Exec/Lock Time: 10 / 1 sec.
use C244045_joom2; UPDATE `jos_session` SET `time`='1269935927',`userid`='76',`usertype`='Registered',`username`='subscription',`gid`='18',`guest`='0',`client_id`='0',`data`='__default|a:8:{s:15:"session.counter";i:1075;s:19:"session.timer.start";i:1269923356;s:18:"session.timer.last";i:1269935891;s:17:"session.timer.now";i:1269935925;s:22:"session.client.browser";s:83:"Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727)";s:8:"registry";O:9:"JRegistry":3:{s:17:"_defaultNameSpace";s:7:"session";s:9:"_registry";a:1:{s:7:"session";a:1:{s:4:"data";O:8:"stdClass":0:{}}}s:7:"_er ... [too long]

Time: 4% (1093 sec) Amount: 4% (103 queries) Rows Examined/Sent: 136 / 65 Avg. Query Exec/Lock Time: 10 / 2 sec.
SELECT oldurl, newurl FROM jos_redirection WHERE oldurl = 'Current-Issue/components/com_adagency/views/adagencyreports/tmpl/rotator.php/' ORDER BY rank ASC LIMIT 1;

Time: 4% (1059 sec) Amount: 4% (111 queries) Rows Examined/Sent: 0 / 0 Avg. Query Exec/Lock Time: 9 / 0 sec.
use C244045_joom2; UPDATE jos_redirection SET cpt=(cpt+1) WHERE oldurl = '20100322252/components/com_adagency/views/adagencyreports/tmpl/rotator.php';

Time: 3% (915 sec) Amount: 4% (96 queries) Rows Examined/Sent: 0 / 0 Avg. Query Exec/Lock Time: 9 / 0 sec.
UPDATE jos_ad_agency_stat SET `how_many` = '376' WHERE substring(entry_date,1,10)='2010-03-30' AND ip_address=-886192853 AND campaign_id=6 AND banner_id=17 AND `type`='impressions' limit 1;

Time: 2% (649 sec) Amount: 2% (65 queries) Rows Examined/Sent: 13585 / 65 Avg. Query Exec/Lock Time: 9 / 1 sec.
use C244045_joom2; SELECT id FROM jos_content WHERE `title`="404";



Total Time : 19783 sec / Date : 2010-03-31

Open in new window

LVL 11
Amanda WatsonWeb DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Member_5340450Commented:
This is a function you will find in phpmyadmin. Do you have access to that tool?

0
Amanda WatsonWeb DeveloperAuthor Commented:
Yes I do actually to this one...what do I do?
0
Member_5340450Commented:
Select the database from the choices on the left

On the Structures page, go to the bottom, check all

Select Optimize table

If there's more than one page of tables do this to all pages.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amanda WatsonWeb DeveloperAuthor Commented:
Nice and easy..hope it works..thanks,
A
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.