[mythtv-users] Slow MySQL query after delete

[Michael T. Dean]

On 09/05/2007 04:36 AM, David Rees wrote:
> On 9/4/07, Michael T. Dean <mtdean at thirdcontact.com> wrote:
>   
>> Basically, one of the biggest benefits is that we could move all efforts
>> to supporting embedded MySQL 5.5 or whatever and could drop support for
>> MySQL 3.23, MySQL 4.0, MySQL 4.1, MySQL 5.0, ...  There's a /lot/ of
>> performance-draining "backwards compatibility" in the code we have now.
>> If all users had the same DB engine, things would be a /lot/ easier.
>>     
> You really should think about dropping support for anything older than
> 4.1. What's the point in maintaining anything that the upstream vendor
> doesn't support themselves?
>   

Talk to the distros that are currently in use.  More than that, go out
and talk to the people who are still running MySQL 3.23 or ...  You may
want to volunteer to upgrade and support their databases.  Otherwise, it
may seem a bit demanding of you to force them to upgrade.

Besides, technically, 4.0 and 4.1 are both supported.  See
http://www.mysql.com/company/legal/lifecycle/#calendar .

>> Maintaining support for all the "shipping in standard distributions"
>> versions of MySQL is hard enough.  Add in totally different DB engines
>> and things get exponentially worse.  Does the company you work for spend
>> the money to make truly DBMS-independent applications?  So, why should
>> Myth do so?  (If the answer to the first question was, "Yes," then
>> nevermind--I won't be able to catch you up. :)
>>     
> My day job involves writing backend code for high performance
> websites. While I don't need to write database independant code, I use
> tools (Java and Hibernate) that allow me to swap backends with a
> minimal amount of fuss. With Hibernate, all I have to do is swap out a
> couple configuration lines and the migration is complete, no code
> change required.

You're doing the right thing by using Hibernate, but IMHO, blindly
pursuing "database independence" is far worse than tying yourself to a
single DB engine.  Code is expensive, so code that you don't have to
write (i.e. code provided by some database-specific functionality) is a
good thing.  Just like using code provided by Hibernate is a good
thing.  Creating your own lesser quality code--lesser quality not
because of your skills, but because there's no way you have the
time/budget to spend on creating something from scratch that's as good
as Hibernate--would be just plain stupid (or arrogant).  But, the
argument for Hibernate (or, at a lower level, JDBC or ...) isn't
database independence, it's code reuse.  The only way that the argument
for Hibernate could also include database independence is if there's a
business requirement for database independence.  Have you ever even
asked what the business requirement for database independence is?  (And,
if the business requirement falls into the "insurance" category (in case
it's too expensive to renew the license, in case the DBMS vendor goes
out of business, in case the DBMS vendor doesn't provide timely support
for Windows Server 2008, ...), that's something that Open Source
software doesn't have to worry about--so, I don't worry about it.)

>  As a bonus, it also forces me to abstract away 99% of
> SQL code so it's trivial to write a backend process that handles all
> the SQL and implements client interfaces of your choice. And the
> amount of work that goes into maintaining that abstraction layer is no
> more than maintaining a the interface to a single database.
>   

Though testing is a whole other matter.  Especially when you're testing
for thousands of differently-configured different DBMS's/databases
worldwide.

> Using Hibernate, I've personally swapped backends between MySQL,
> Sybase and Postgres without changing a line of code.
>   

However, before doing that, how much testing did you have to do?  3x the
testing?  9x the testing?  When you're working on a project for fun,
increasing the amount of work required for the most tedious, boring, and
unappreciated part of the job is not something most want to do.

And, on how many different databases (not DBMS engines) is your
application run?  And how many of these DBMS's/databases were installed,
configured, and maintained by someone outside your company?  And how
many are installed, configured, and maintained by someone who has no
interest in learning about installing, configuring, and maintaining a
DBMS?  "Knowing" that you have database independence is much easier when
you maintain strict control over the test systems.

But, wait, shouldn't you all be writing the code so it can work on
Oracle and DB2, also?  Shouldn't you write it so it works with Derby and
Firebird and Ingres and ...

> Certainly something similar exists for C++? I'm not much of a C/C++
> programmer so I've never looked, but I have used perl DBI libraries
> for similar jobs (though none very big).

Feel free to write support for other DB's into Myth.  Even if the devs
don't accept your patches, you have the option to fork (and then
maintain and test the DB independence on your own).

Mike