[wp-hackers] geocomments?

[Stewart Ugelow]

On 4/13/06, Owen Winkler <ringmaster at midnightcircus.com> wrote:
> 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.

So what happens if you have your two coolers of beer and soda and then
your friend shows up at your party with a case of iced tea?

You have to file a trac ticket asking Matt or Ryan for permission to
buy another cooler, everyone at the party gets to comment about
whether iced tea is an essential beverage to the party core, and you
have to write a whole new set of instructions on where the iced tea is
located and what the process is for grabbing one if you want one. Then
those instructions have to be maintained and updated every time you
throw a party going forward.

The whole process can be a real mess. Just ask the guy who wrote
http://www.asymptomatic.net/2006/01/17/2216/no-more-feeds-for-wordpress/.

> > 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.

But I guess I'm trying to understand is if multiple WHERE clauses
impede the performance of DB queries to such a degree that people feel
so strongly about having multiple redundant table structures, why
aren't WP's DB queries optimized to use one WHERE clause whenever
possible?

How do you decide where the tradeoff of having to loop through
slightly larger result sets with PHP (if for example you used "SELECT
meta_key, meta_value FROM $table WHERE $id_col = '$id'") outweighs the
extra WHERE clause?

Or alternately why not add a unique search key column of something
like md5(id_col.meta_key) to the table so you'd only have one WHERE
clause in your query?

I realize that DB performance tuning can be more art than science, but
after reading Ryan's recent post
(http://boren.nu/archives/2006/04/03/romancing-the-database/) about
the very real impact that query tweaking can have, I'm newly curious
to know what the various assumptions are behind some of these DB
decisions.

--Stewart

--

Stewart Ugelow
www.ugelow.com