In my previous post on implementing a custom field search in WordPress, I showed how to modify the internal SQL LIKE search that is the default search engine of WordPress. There are other search engines that WordPress can make use of, most notably Sphinx Search.

Sphinx is a great open-source full-text search engine. I have used it several times to implement search functionality in content management systems. I didn't go into using Sphinx with WordPress initially because it is a lot more work. The default WordPress search is an SQL query performed by PHP. Sphinx is composed of an indexer, a search daemon, and a client library and the installation of the package is unavailable in shared-hosting environments.

Since more and more people are using WordPress on dedicated servers, I'm not surprised that questions on implementing custom field searching using Sphinx has arrisen. The good news is that it is very easy to implement! All you have to do is update the sphinx.conf to pull out the custom fields as fields in the sql_query of your sources.

In my previous example, I worked with the fields: bio, byline, kicker, and deck. I'll use these same fields again for continuity sake. I'm also using those fields because I have a site that uses these fields for custom search and I could test the changes needed for Sphinx.

In order to add each field, we have to do three things. Add the column to the select on the wp_post, add a left join to the select on the wp_post, and add a placeholder to the select on wp_comments.

Example Columns:
p.post_content as body, \ t.name as category, \ bio_meta.meta_value as bio, \ byline_meta.meta_value as byline, \ kicker_meta.meta_value as kicker, \ deck_meta.meta_value as deck, \ IF(p.post_type = 'post', 1, 0) as isPost, \ 0 as isComment, \
Example Joins:
inner join \ {wp_terms} t on (tt.term_id = t.term_id) \ left join \ {wp_postmeta} bio_meta on (p.ID = bio_meta.post_id and bio_meta.meta_key = 'bio') \ left join \ {wp_postmeta} byline_meta on (p.ID = byline_meta.post_id and byline_meta.meta_key = 'byline') \ left join \ {wp_postmeta} kicker_meta on (p.ID = kicker_meta.post_id and kicker_meta.meta_key = 'kicker') \ left join \ {wp_postmeta} deck_meta on (p.ID = deck_meta.post_id and deck_meta.meta_key = 'deck') \ where \
Example Placeholders:
c.comment_content as body, \ '' as category, \ '' as bio, \ '' as byline, \ '' as kicker, \ '' as deck, \ 0 as isPost, \
I have also attached the sphinx.conf distributed with the WordPress plugin so that you can see a more complete example of the implementation.

I hope everyone enjoys this as much as they did the last!

Andres Carvajal Says:	May 20th, 2009 at 11:08 pm
Hey John, this was really helpful! I don’t think there’s another site which explains how to do it. Thank you very much, Andres.

JamesD Says:	June 11th, 2009 at 1:26 pm
Thanks for the useful info. It’s so interesting

kamal Says:	June 22nd, 2009 at 7:10 pm
Hi, and thanks for the info, as i’m bad in english and php :p i would like to know wher can i download you plugin? Thanks

John Hoff Says:	June 22nd, 2009 at 7:47 pm
My post concerned how to modify an existing plugin, Sphinx Search, to perform searches on custom fields. I have not created a plugin to handle custom fields automatically. The primary reason for this is that using custom fields heavily in a WordPress installation already requires a lot of customization–so adding another plugin into the mix isn’t really necessary.

Jörn Says:	November 17th, 2009 at 10:49 am
Hi, thanks for this! i have a serialized Array in one meta_value. Is there a way to make them searchable too?

John Hoff Says:	November 18th, 2009 at 5:09 am
Using a serialized array does get tricky. You have to unpack part of the array in the SQL statement and only use SQL to do it. Say you are given meta_value along the lines of: custom.meta_value = "aaa,bbb,ccc" You can unpack that and assign each piece to an sql column: SUBSTRING_INDEX(SUBSTRING_INDEX(custom.meta_value, ",", 1), ",", -1) as first, SUBSTRING_INDEX(SUBSTRING_INDEX(custom.meta_value, ",", 2), ",", -1) as second, SUBSTRING_INDEX(SUBSTRING_INDEX(custom.meta_value, ",", 3), ",", -1) as third, You might also need to use TRIM to remove any quote marks. Hope that points you in the right direction.