Performance Problems with Complex Meta Queries

[dominicp]

I have noticed that when I have several criterion on a WP_Query meta query it can start to drastically impact performance. I don't know if this is just an unavoidable consequence of using the key/value data structure that WordPress does, but I wanted to bring it to the dev's attention now as it seems like 3.4 is going to involve some serious work on the Query component.

I have outlined a specific example of the problem in some detail here: ​http://stackoverflow.com/q/8893160/931860, and I can definitely provide more details if someone feels they would be relevant.

[scribu]

The only thing we can do here is add an index to the meta_value column.

Since meta_value is of type LONGTEXT, the index needs to be limited to a certain length, which is up for debate.

[dominicp]

I'm not a MySQL expert, but someone said on the stackoverflow question that that might not help (​http://stackoverflow.com/questions/8893160/mysql-wordpress-slow-query-when-using-in-statements/8893207#comment11120031_8893207).

I tested out creating a 10 character index, and I didn't see any impact on performance in my particular case.

[scribu]

Well, the table layout is not going to change, so that's that. You might consider using Pods or some other plugin which uses custom tables.

In general, when you find yourself doing a lot of IN queries on meta, consider using a taxonomy instead.

[dominicp]

Thanks for the helpful tip. I will definitely look into using taxonomies instead of post meta.

I agree that the table layout shouldn't change. I just wanted to bring up the performance issues I was seeing in case it was something that could be addressed within the current table layout. Thanks for taking a look.

[dominicp]

In case it will help someone, I wound up using the STRAIGHT_JOIN MySQL optimizer hint on my complex meta query (in addition to taking scribu's advice on switching to taxonomies), and the performance was dramatically improved.

I don't know why, and it might be a terrible idea in the long run, but I figured I'd post this in case anyone else can benefit. I've documented the results more fully on the StackOverflow question I referenced initially.