Sackgesicht VIP
Total posts: 1,636
23 May 2013 22:16

While looking into the debug sql info, i noticed a lot of queries for the table #_js_res_favorite in my list view.

It turned out to be a subquery.

SELECT r.*,(SELECT id 

  FROM #_js_res_favorite 

  WHERE record_id = r.id 

  AND user_id = 53) as bookmarked,0 as `new` 

  FROM #_js_res_record AS r 

  WHERE r.section_id = 4 

  AND r.ctime < '2013-05-24 02:01:59' 

  AND (r.extime = '0000-00-00 00:00:00' OR r.extime > '2013-05-24 02:01:59') 

  AND r.parent_id = 0 

  AND r.archive = 0 

  AND r.published = 1 

  AND r.hidden = 0 

  AND r.id IN(97868,0) 

  ORDER BY r.ctime ASC 

  LIMIT 0, 10

But since i disabled the bookmark functionality in TYPE settings ..

--> Who can bookmark: No One

I would not need this query, right?

Why not removing it or just leaving it empty like the "new" column in the example?

Another comment to the IN() function:

AND r.id IN(97868,0) 

As you can see in the code example it always includes a zero value at the end. Since there is no "0" ID it is also useless and just adds additional comparison to the query. Better remove it.

Comment to the time .. the query time is also wrong (minus 8 hours). Does it have an impact on the expiration time of records? .. might check on this later ...

Last Modified: 02 Mar 2014


Sergey
Total posts: 13,748
24 May 2013 00:16

The problem with this subquery is that we do not know if you turn favorites off or not. because favorites is a type parameter and this is section query. I mean to find out I need to select all types in section ans cycle it and check parameters.

But i have found way around it and fixed it.

Another comment to the IN() function:

This i cannot fix. I think that 0 will not add to many comparisons. Especially when it is primary key and search by primary key is super quick.

Powered by Cobalt