[Suggestion] Use index table for ordering

[ManuParticipant]

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 !

[ManuParticipant]

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.

[Ernest MarcinkoKeymaster]

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 :)

[Author]

Posts