Productbook extension and Mysql queries problem - Joomla! Forum - community, help and support

help!

i have been developing site, , it's brilliant, has 1 problem...

accessing archives (using productbook) creates hang on server.  i've been looking, , can't seem debug this!
the query in question (i ran in debug session) this:

code: select all

 select * jos_productbook
 left join jos_productbook_catg c on c.cid=a.catid
  a.published ='1' , a.catid ='2' , a.approved = 1
 and c.access<='0'
 order a.id desc limit 0,10

there twenty pages using code, , make hang thirty seconds.  page takes 2 seconds load, because esle optimised, cached, etc following many speed guides.  bottleneck...

the php code :

code: select all

<?php
      if ($catid <> "") {
         echo showcategorypathlink($catid);
      } else if ($id) {

         $database->setquery("select a.*, cc.name category "
         . " \n #__productbook a, #__productbook_catg cc "
         . " \n a.catid=cc.cid , a.id=$id "
         . " \n , cc.access<='$gid'");
         
         $rows = $database->loadobjectlist();
         $row = &$rows[0];
         echo showcategorypathlink($row->catid);
      }

anyone got ideas?  can't see how seems pause...  mysql has cache on, site has cache on, waits same amount of time each page...

r

hm. 2 examples not identical. in following, i'm going reference 1 of them.

you should try , determine whether tables involved indexed properly. if have right access credentials, can have @ slow query log (if enabled). make sure tables optimised , key information updated using "analyze table" command (mysql might inadvertently using wrong indexes, if @ all).

if have access phpmyadmin or similar, try , run following in sql dialog (this query debug session):

code: select all

explain select * jos_productbook left join jos_productbook_catg c on c.cid=a.catid a.published ='1' , a.catid ='2' , a.approved = 1 , c.access<='0' order a.id desc limit 0,10

notice "explain" in front of select command. tell mysql show how performing db up. valuable command!! worst scenario doing full table scan. grind system halt if have many rows , executing query many times since mysql has through each , every row.

read mysql documentation optimizing queries using explain :

http://dev.mysql.com/doc/refman/4.1/en/explain.html

an article worth checking out regarding index creation on basis of "explain" results here :

http://www.databasejournal.com/features/mysql/article.php/1382791

a site worth visiting mysql performance is:

www.mysqlperformanceblog.com

they have friendly forum.

ford

you're going doing experimenting result.

if in doubt how interpret output of above command, can try , paste here , i'll have @ it.