MySQL user's Conference

[Bradley Baetz]

On Fri, Apr 18, 2003 at 11:41:49AM -0700, Myk Melez wrote:
> I think it's more the case that certain kinds of subqueries won't be 
> usable until 5.0, while other kinds will be just fine.

They kind I'm interested in is changing all the inner/outer joins in
buglist.cgi into subselects. This makes lots of our outer joins (which
we have to do for the OR case) become inner joins most of the time.

> Right, I think.  It's hard to tell because Solaris grabs all free memory 
> to use as disk cache, so it's hard to know how much memory we have free 
> or what would happen if we took it away from disk cache and gave it to 
> MySQL.

I don't think it would matter that much. With 4G of RAM, given that most
of bmo's data is attachments, and we don't really need those cached, if
mysql caches data instead of the os, it would theoretically be better.

> Actually, I think it would.  Currently we search the attachments table 
> in attachment flag queries, which happen all the time.  Compressing the 
> data would make that table much smaller and easier to scan (although 
> perhaps indexes work around this problem).  This could also be solved by 
> putting the attachment data in a separate table, of course.  The other 
> way it would help is that I wouldn't always be having to deal with the 
> Solaris 2GB+ bug that prevents me from operating on a DB tar file with 
> common system utilities (like gzip and scp).

Eww. You mean that BLOB'd data is stored inline with the rest of the
data? Is that true even for innodb? (For comparison, oracle will store
<4K worth inline, but if its larger than that its all placed out of
line. PG just stores all LOB datatypes separately. I don't _think_ it
inlines small ones, but I'm not sure)

> >We do too many ad-hoc queries for that to work.
> >
> I think we probably have some common patterns that could be exploited, 
> f.e. many queries select a product and one or more statuses.

Sure, but once status move off into a different table, it wouldn't work,
because you'd have to join on two differnet tables with the same index.
Also, that won't work if the user selects more than one status, because
mysql doesn't optimise OR evne if it could use the same index. (which is
why we convert to IN in buglist.cgi, but I don't think that works if
there are multiple columns involved.)

> It got stalled on privacy issues.  Everyone who gets to see the data has 
> to agree to keep it private, which involves work if that group is 
> everyone with access to landfill.  Our other option is to make a legal 
> determination that the rules do not apply in our case, which also 
> involves work.

Can't you just munge the data, though? There was that script I hacked up
for doing exactly that...

> No.  My thinking is that Bugzilla 2.18 should support 3.23, and then we 
> should move to 4.0 for 2.19+.

Hmm. IF we get to use innodb then, and use transactions, I'm definately
for it.

> 
> -myk
> 

Bradley