Support for multiple "Locations" in bugzilla

[Bradley Baetz]

On Sat, Mar 01, 2003 at 11:25:26AM +1100, Bradley Baetz wrote:
> 
> I want mysql to go away :) But yeah, its not happening in the short
> term.

... and theres a reason for this, BTW. Consider a query to find all the
bugs where I am the assignee, or have added a comment, using
postgresql-7.4cvs (ie not released), and mysql-3.23.54a

For 20,000 users, 100,000 bugs, and 1,000,000 comments randomly
distributed:

> select distinct bugs.bug_id FROM bugs LEFT JOIN longdescs
  USING(bug_id) WHERE (bugs.assigned_to=86 OR longdescs.who=86);

mysql: 3.33 sec
postgres: 14423.07 msec [I can get this down to 11758.74 msec by changing a
                         config option - I should probably file a bug on that]

(returning 46 rows)

Now, that looks pretty bad for pg. _BUT_, that query isn't what we
really wanted to know.

> SELECT bugs.bug_id FROM bugs WHERE bugs.assigned_to=86 OR bugs.bug_id
  IN (SELECT bug_id FROM longdescs WHERE longdescs.who=86)

mysql: error
postgres: 137.66 msec

(The rather large disclaimer here is that that last result needs
cvs-postgres to be smart with the subselect. And before anyone asks, the
first query isn't the same as the second without the distinct, and pg
doesn't pass the distinct from the top down to lower layers, AFAIK, so
this can't be easily done automatically by the optimiser)

I can get it down to 0.65 msec (no, that is not a typo) if I use a union
query, but thats not the sort of thing we can generate from
Bugzilla::Search ourselves, its more a job for the optimiser, which
probably isn't useful in most queries - its just because this one has no
other conditions.

Now, heres something even more fun. With innodb tables, the first query
takes 111.62 sec, +/- 10sec. 4.0 may be faster I guess, and I haven't
played with any of the (many) tuning paramaters. I also may have just
found a particularly bad test case or something.

All times are from the 2nd run through, plus after a VACUUM ANALYZE for
pg.

Bradley