SQL query performance problem

udaraaka

Joined: 2012-02-03
Posts: 29
Posted: Mon, 2012-09-03 18:13

Hi, have a website runs with gallery3 and it has 40000+ images. Today I just checked by MySQL slow query log and found out that there is a very slow query

# Query_time: 1.318432 Lock_time: 0.000092 Rows_sent: 42707 Rows_examined: 87568
use freewal3_begi;
SET timestamp=1346668464;
SELECT `items`.*
FROM `items`
WHERE (`items`.`view_1` = '1') AND `type` <> 'album'
ORDER BY `created` DESC

As you can see it has examined 87568 and returned all the content of items table, so I think the respose data size is very big too because it selects * from items.

I dont know which page has generated this query, but I think it is not essential to fetch the whole items table for one page view. Any Ideas to optimize this query?
 
floridave
floridave's picture

Joined: 2003-12-22
Posts: 27300
Posted: Mon, 2012-09-03 18:37

Could be a module that is generating that. Hard to tell.

Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team
 
udaraaka

Joined: 2012-02-03
Posts: 29
Posted: Tue, 2012-09-04 02:22

floridave are you sure that the above query is not generated by gallery 3 core?
 
floridave
floridave's picture

Joined: 2003-12-22
Posts: 27300
Posted: Tue, 2012-09-04 02:29

I don't know.

Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team
 
floridave
floridave's picture

Joined: 2003-12-22
Posts: 27300
Posted: Tue, 2012-09-04 04:34

Possibly the RSS module:

      $all_items = ORM::factory("item")
        ->viewable()
        ->where("type", "<>", "album")
        ->order_by("created", "DESC");

Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team
 
udaraaka

Joined: 2012-02-03
Posts: 29
Posted: Tue, 2012-09-04 16:58

floridave you are right. I tried to access RSS feeds of my site and checked the slow query log and saw the same query again. But those pages only dispalys the latest galleries, comments etc only. fetching complete table is not necessary. Then why fetch them all?
 
floridave
floridave's picture

Joined: 2003-12-22
Posts: 27300
Posted: Tue, 2012-09-04 18:57

I guess it has to look at all items even deeply nested albums to see if you added a new item?
I'm no SQL or RSS expert.

Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team