Discussion: WP_Query, meta_query, meta_value CAST and MySQL indexing

[kovshenin]

Hey everyone, thanks for stopping by! A little bit of history, okay? I was working on a real estate project quite some time ago, somewhere around 3.0 beta and I was using post meta to store things like listings features (has-pool, has-wireless, has-parking) along with other things. Meta queries in WP_Query were not allowed at that time so I queried the database directly using $wpdb and queries were like:

SELECT post_id FROM wp_postmeta WHERE meta_key = 'has-pool' AND meta_value = '1';

I know it's not the best I could do but creating an index on ( meta_key, meta_value ) helped improve our search performance a lot. Anyhow..


I was thrilled to see meta_query after a while and rushed to recode everything to use the new API, but soon found out that all my queries weren't using my index at all and after some Googling and experimenting I found the problem was in the CAST() statement which made the query fall back to using the ( meta_key ) index which of course was much slower in my case.

( Can't embed the EXPLAIN statement here nicely, so here you go: https://gist.github.com/1247528 )

I understand that CAST() is required when working with integers, dates, times, etc but from what I can see CAST( meta_value AS CHAR ) doesn't really do anything, does it? Unfortunately I haven't found a way to create a "casted" index which would actually be great and solving my CHAR issue is only one thing, since real estate listings have things like price, number of bedrooms, etc. which are numeric values.


I haven't got any good thoughts for a global solution yet, but the first thing that came into my mind was typed fields, like when adding "0" to a meta field would add "0" to meta_value, 0 to meta_value_numeric, and so on. Redundant? Ineffective storage? I don't know. Can anybody think of a way to create a plugin that would do that?

Hope this would make an interesting discussion and thanks in advance for participating!
~ Konstantin