Search queries

[newbie LAC]

Hello,

Url

http://localhost/443/index.php?/search/&q=hello&type=forums_topic&nodes=2&search_and_or=or&sortby=relevancy

I select posts/topics from forum with ID 2

But the queries looks like

SELECT main.*, ((MATCH(index_title) AGAINST ('hello' IN BOOLEAN MODE)*5)+(MATCH(index_content,index_title) AGAINST ('hello' IN BOOLEAN MODE)))/POWER(( ( UNIX_TIMESTAMP( NOW() ) - ( CASE WHEN index_date_updated <= UNIX_TIMESTAMP( NOW() ) THEN index_date_updated ELSE 0 END )) / 3600 ) + 2,1.5) AS calcscore 
FROM `core_search_index` AS `main` 
WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\forums\Topic\Post' OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\cms\\Records\\Review1') ) OR index_class='IPS\cms\Pages\PageItem' OR ( index_class IN('IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) ) AND ( ( index_class='IPS\forums\Topic\Post' AND ( index_container_id IN('2') ) ) ) AND MATCH(index_content,index_title) AGAINST ('hello' IN BOOLEAN MODE) AND ( index_permissions = '*' OR ( FIND_IN_SET(4,index_permissions) OR FIND_IN_SET('m1',index_permissions) OR FIND_IN_SET('cm',index_permissions) ) ) 
ORDER BY calcscore DESC 
LIMIT 0,25

SELECT COUNT(*) 
FROM `core_search_index` AS `main` 
WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\forums\Topic\Post' OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\cms\\Records\\Review1') ) OR index_class='IPS\cms\Pages\PageItem' OR ( index_class IN('IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) ) AND ( ( index_class='IPS\forums\Topic\Post' AND ( index_container_id IN('2') ) ) ) AND MATCH(index_content,index_title) AGAINST ('hello' IN BOOLEAN MODE) AND ( index_permissions = '*' OR ( FIND_IN_SET(4,index_permissions) OR FIND_IN_SET('m1',index_permissions) OR FIND_IN_SET('cm',index_permissions) ) )

Pay attention on WHERE

Why do you need next part?

( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\forums\Topic\Post' OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\cms\\Records\\Review1') ) OR index_class='IPS\cms\Pages\PageItem' OR ( index_class IN('IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) )

---

Similar issue with leaderboard

SELECT main.* FROM `core_search_index` AS `main` 
WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\forums\Topic\Post' OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\cms\\Records\\Review1') ) OR index_class='IPS\cms\Pages\PageItem' OR ( index_class IN('IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) ) AND ( ( index_class='IPS\forums\Topic\Post' AND ( index_object_id IN(1,3) ) ) ) AND ( index_permissions = '*' OR ( FIND_IN_SET(4,index_permissions) OR FIND_IN_SET('m1',index_permissions) OR FIND_IN_SET('cm',index_permissions) ) ) 
LIMIT 0,50

In the code ( I mean php) you only select reacted classes but in the query again use all

[bfarber]

It's a gremlin in the code with the way the query is built in the search handler. Admittedly with a little effort we could clean it up, however in my own performance testing I haven't noticed any difference in performance with the simplified version of the query, so we just haven't bothered.