<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body>
Bradley Baetz wrote:<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<blockquote type="cite">
<pre wrap="">User-Defined Functions
User-defined functions are useful for things MySQL doesn't provide,
especially when extracting data from the database, processing it, and
then putting it back into the database is expensive. Can be written in
C++, Perl, or presumably other languages, but doesn't make much sense to
write them in scripted languages; you might as well just do them in your
app.
</pre>
</blockquote>
<pre wrap=""><!---->
thats not actually true. Its useful for abstraction, plus you can run
teh comparison at the DB level, eg as part of a VIEW column, or as a
constraint on the query. If its not speed criticial, then perl would be
a lot easier than c++. PL/SQL would be easier, mind you - are they
planning PL/SQL support (or some varient)? If it is speed critical, a
functional index would be nice.
</pre>
</blockquote>
Well, I didn't say it makes no sense. The speaker suggested that one
good use for them would be to implement stored procedures. I didn't
hear anything about PL/SQL at the conference, but stored procedure
support is going to be multi-language, although they are starting with
their own language for testing purposes.<br>
<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<blockquote type="cite">
<pre wrap="">Subqueries
There are two kinds of subqueries: dependent/correlated, which rely on
tables/columns from parent query (and execute once for each row of the
parent query), and independent/noncorrelated, which don't and tend to be
more efficient. Joins are generally more performant than subqueries at
the moment in 4.1; subquery optimization is scheduled for 5.0.
</pre>
</blockquote>
<pre wrap=""><!---->
Hmm. That makes it sound like subqueries won't be usable until 5.0. This
is similar to the problems postges has (but which are fixed in CVS),
where it does a full nested loop join for subqueries.
</pre>
</blockquote>
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.<br>
<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<blockquote type="cite">
<pre wrap="">Types & Examples (in pseudo-code):
* derived tables: SELECT * FROM foo, (SELECT ...) (cannot be dependent)
</pre>
</blockquote>
<pre wrap=""><!---->
Are they allowing SELECT a, (SELECT name FROM lookup_tab WHERE id=b)
FROM table? ie with the subselect in the from clause?
</pre>
</blockquote>
I think I saw an example of that in the presentation.<br>
<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<blockquote type="cite">
<pre wrap=""> * don't use DBD::mysql < 2.1026 with locks (because of its
auto-reconnect feature, which you can't disable without recompiling)
</pre>
</blockquote>
<pre wrap=""><!---->
Hmmm. Do we need to up our requirements, or is that only an issue if you
use connect_cached/Apache::DBI ?</pre>
</blockquote>
I think we have to up our requirements, but maybe we should email Tim
about this.<br>
<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<blockquote type="cite">
<pre wrap="">Optimizing MySQL
(Jeremy Zawodny)
* MySQL is tuned for small-medium data sets and systems by default.
If you have lots of memory you need to configure it to use it.
</pre>
</blockquote>
<pre wrap=""><!---->
Is there tuning we can do? bmo has massive ammounts of RAM, right - its
just CPU bound.</pre>
</blockquote>
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.<br>
<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<blockquote type="cite">
<pre wrap=""> * Compress data if possible. gzip is quick, cheap, and makes things
faster, but you can't use it if you want to search the data.
</pre>
</blockquote>
<pre wrap=""><!---->
That doesn't apply to us, except for attachments, and I don't think that
that woudl help anything we do.</pre>
</blockquote>
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).<br>
<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<blockquote type="cite">
<pre wrap=""> * Understand the leftmost prefix rule for indexes, and note that
MySQL will only ever use one index per table, so you need to
create compound column indexes if you want it to use two indexes
(f.e. how about product_id and bug_status in Bugzilla?)
</pre>
</blockquote>
<pre wrap=""><!---->
We do too many ad-hoc queries for that to work.</pre>
</blockquote>
I think we probably have some common patterns that could be exploited,
f.e. many queries select a product and one or more statuses.<br>
<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<pre wrap="">What happened to the plan to give developers access to the bmo data? I'd
love to run some real pg vs mysql comparision tests.
</pre>
</blockquote>
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.<br>
<br>
<blockquote type="cite" cite="mid20030418025959.GA2565@mango.home">
<blockquote type="cite">
<pre wrap=""> * Use a UNION to break a query not using two indexes into two, each
using one.
</pre>
</blockquote>
<pre wrap=""><!---->
We really can't do that for buglist.cgi. Plus UNION is a new 4.0 thing,
so we can't use it at all yet.
</pre>
</blockquote>
No. My thinking is that Bugzilla 2.18 should support 3.23, and then we
should move to 4.0 for 2.19+.<br>
<br>
-myk<br>
<br>
</body>
</html>