[Suggestion] Use index table for ordering

Home Forums Bug reporting, Questions & Suggestions Ajax Search Pro for WordPress [Suggestion] Use index table for ordering

This topic contains 2 replies, has 2 voices, and was last updated by Ernest Marcinko Ernest Marcinko 1 week, 6 days ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #41105
    Manu
    Manu
    Participant

    Hi Ernest,

    I’m ordering my results by “user rating count” and i have requests lokking like that :

    SELECT
    			asp_index.doc as id,
    			asp_index.blogid as blogid,
    			'pagepost' as content_type,
    			
    		IFNULL((
    			SELECT
    			aspp.priority
    			FROM wp_ajaxsearchpro_priorities as aspp
    			WHERE aspp.post_id = asp_index.doc AND aspp.blog_id = 1
    		), 100)
    		 AS priority,
    			1 AS p_type_priority,
    			0 AS post_author,
    			1  as customfp,
    			(SELECT IF(meta_value IS NULL, 0, meta_value)
    			FROM wp_postmeta
    			WHERE
    				wp_postmeta.meta_key='rating_count' AND
    				wp_postmeta.post_id=asp_index.doc
    			LIMIT 1
    			)  as customfs,
    			'' as date,
    			0 as menu_order,
    			'' as title,
    			asp_index.post_type AS post_type,
    			1 AS group_priority,
    			(
    				 asp_index.title * 300 * 0 +
    				 asp_index.content * 0 * 0  +
    				 asp_index.excerpt * 0 * 0  +
    				 asp_index.comment * 0 * 0  +
    				 asp_index.link * 0 * 0 +
    				 asp_index.tag * 0 * 0  +
    				 asp_index.customfield * 0 * 0  +
    				 asp_index.author * 0 * 0
    			) AS relevance
    		FROM
    			wp_asp_index as asp_index
    			
    		WHERE
    				(asp_index.term_reverse LIKE 'emèrc%' AND asp_index.term_reverse <> '')
    			AND (asp_index.post_type IN ('recipe') )
    			AND asp_index.blogid = 1
    
    			AND (1)
    
    			AND (asp_index.content = 0 AND asp_index.excerpt = 0) 
    			
    		LIMIT 90000;

    Everything is working well but it’s take a lot of time : my postmeta table is very huge and i would like to avoid to access it for search.

    It is already possible to index post_meta in the index table,
    do you think it can be possible to use this indexed post_meta data for ordering and not for search trought a future option in the admin ?

    Thank you !

    #41106
    Manu
    Manu
    Participant

    Edit : I have found a solution using the index for postmeta table provided by https://wordpress.org/plugins/index-wp-mysql-for-speed/
    The same request is taking now 0,04 second instead of 2 seconds.

    #41108
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    Thank you. Unfortunately indexing that data into a separate table would generate the same problem over time.

    I belive the plugin you mentioned is the best way of dealing with the issue. Whenever possible it is best to avoid metadata table queries, but an index key is an amazing idea.

    I will add this plugin to the documentation as a suggested performance upgrade feature, it looks very well made, I just reviewed the source code.

    Best,
    Ernest Marcinko

    If you like my products, don't forget to rate them on codecanyon :)


Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.