MySQL user's Conference

[Myk Melez]

Bradley Baetz wrote:

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

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

>>Types & Examples (in pseudo-code):
>>
>>   * derived tables: SELECT * FROM foo, (SELECT ...) (cannot be dependent)
>>    
>>
>
>Are they allowing SELECT a, (SELECT name FROM lookup_tab WHERE id=b)
>FROM table? ie with the subselect in the from clause?
>  
>
I think I saw an example of that in the presentation.

>>   * don't use DBD::mysql < 2.1026 with locks (because of its
>>     auto-reconnect feature, which you can't disable without recompiling)
>>    
>>
>
>Hmmm. Do we need to up our requirements, or is that only an issue if you
>use connect_cached/Apache::DBI ?
>
I think we have to up our requirements, but maybe we should email Tim 
about this.

>>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.
>>    
>>
>
>Is there tuning we can do? bmo has massive ammounts of RAM, right - its
>just CPU bound.
>
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.

>>   * 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.
>>    
>>
>
>That doesn't apply to us, except for attachments, and I don't think that
>that woudl help anything we do.
>
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).

>>   * 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?)
>>    
>>
>
>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.

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

>>   * Use a UNION to break a query not using two indexes into two, each
>>     using one.
>>    
>>
>
>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.
>  
>
No.  My thinking is that Bugzilla 2.18 should support 3.23, and then we 
should move to 4.0 for 2.19+.

-myk

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bugzilla.org/pipermail/developers/attachments/20030418/b09a3979/attachment.html>