[wp-hackers] geocomments?

[Owen Winkler]

Stewart Ugelow wrote:
>> All of which is more efficient than querying against a single table with
>> an extra WHERE parameter, indexed or not.
> 
> Really? Even if it's a multi-column index as described at
> http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html?

Yeah.  Think of it like this:

You're having a party and are putting drinks in coolers.  Is it easier 
to find a bottled Guiness in a cooler that contains only beer, or in a 
container that contains all varieties of beer and soda?

Even if you kind of segregate the bottles in a single cooler (use an 
index), people shuffling the bottles around as they are added and 
removed requires that they either be re-segregated periodically or that 
a less efficient search be employed later in the party.  No matter how 
well the bottles are organized, it's still going to be easier to choose 
the correct one of two coolers to get the right type of beverage than to 
store all the drinks in one place.

> And doesn't Mark's patch already have multiple where clauses in the
> wp_get_meta() functions?
> 
> $metalist = $wpdb->get_results("SELECT meta_value FROM $table WHERE
> $id_col = '$id' AND meta_key = '$key'", ARRAY_N);

Yes, but that's the minimum number (2) of required WHERE elements to get 
the job done.  If you split either of those values off as a new table, 
you'd end up with a variable number of tables, which would be less 
efficient than using one for each matching table.

Even though you might have a composite index, which might improve the 
lookup, it would still have to index on three fields.

Whatever marginal gain you might be able to eek out of the whole mess, 
you instantly kill it by what development time it takes to debug stuff 
related to these tables when it isn't working right.  ;)

Owen