I often read articles saying to combine statements and send less queries. But you seldom see advise about splitting queries to improve performance. Here is what I came across just the other day:

SELECT IF(`uid_to`=@user, `uid_from`, `uid_to`) AS `uid`, `message`, `date`, `uid_to`=@user AS received
  FROM `beepme_msg`
  WHERE (`uid_from`=@user AND `uid_to`!=@user) OR (`uid_to`=@user AND `uid_from`!=@user)
  ORDER BY `uid_to`, `date`;

The query gets all messages send and received by the user, filtering our message the user send to himself. This looks like a good query since you’ll get all you data in one call. In reality it will mess up your performance, since it can’t use any indexes and will therefor use a table scan. And as we know, tables scans are slow.

The ‘OR’ statement basically messes this up, since only one index can be used per table. We can see what happens a bit better if …

Event: Learn How to Increase Recoverability of MySQL Databases - presented by BakBone on Tuesday, August 21, 2007

we were able to get out this month?s connector/odbc release a little earlier in the month than usual. one reason we made the release earlier was to get a replacement for last month?s 3.51.17 out there, because that release had an unfortunate bug that caused problems when working with many odbc applications, like microsoft access.

we were also able to get under 90 bugs by fixing a number of other bugs, and working through more of the old bugs and figuring out that they were either already solved or otherwise no longer relevant.

the other reason to get this out earlier in the month has to do with a project that should see some more daylight by the end of the month. more on that when the time …

The idea is as old as Replication is:

How do you know which is the most current slave.

You can use the Master_Binlog_Pos to guess what is most up to date, but which transaction does this binlog position match ?

With the proxy you can add a global transaction ID to your setup, if you let the inject some information into the stream.

The idea is simple and is documented in various places.

Create a MEMORY table which is replicated with a single UNSIGNED BIGINT and increment it at the end of each transaction.

CREATE TABLE trx (
  trx_id BIGINT UNSIGNED NOT NULL
) ENGINE=memory;

INSERT INTO trx VALUES ( 0 );

When ever you commit a transaction UPDATE the trx\_id field:

UPDATE trx SET trx_id = trx_id + 1

Usecases:

• identify which slave is most current and switch to it in case of master failure …

Hello,

Please join us on one of our upcoming webinars entitled "Designing, Evaluating and Benchmarking MySQL Cluster"...

North America
August 8 @ 10 AM PDT

EMEA
August 15 @ 3:00 PM CET

For registration details:

http://www.mysql.com/news-and-events/web-seminars/

In this webinar learn the fundamentals of how to design and select the proper components for a successful MySQL Cluster evaluation. We will explore hardware, networking and software requirements. Work through a basic installation, functional testing. Finally, we will close with an examination of the recent benchmark results performed in conjunction with Intel and Dolphin Interconnect Solutions.

In case you missed Johan's post earlier today, we've also put together an evaluation guide for MySQL Cluster which goes into more detail on the topics covered in the webinar. Look …

How can you evaluate if MySQL Cluster is a good fit for you or not?
Me myself and Jimmy Guerrero (Snr Product Manager) has written a whitepaper about it.

Of course, answering that question is not easy, but in the whitepaper we present ideas for how to make it easier and to increase your chances of success whether it is evaluating MySQL Cluster for a migration project or designing a new mission critical database application where high performance, scalability, and high availability are key requirements.

Jimmy will also have a Webex on the subject:
Wednesday, August 08, 2007, 10:00 am PDT, 1:00 pm EDT, 18:00 GMT
and you are very welcome to register here


Also, I want to mention my friend Jim Dowlings's work on a few scripts …

From OpenSuSE's MySQL 5.0.41-1 changelog:- renamed subpackages according to library packaging policy:
  mysql-shared -> libmysqlclient15, libmysqlclient_r15
  mysql-devel -> libmysqlclient-devel (pulls in both flavors)Thanks. It was time.

I’ve heard from the guys at MySQL that the configuration of Heartbeat communication paths in ha.cf seems to be confusing to some. So, here’s our best practice summary:

• Never configure fewer than two communication paths. And I mean, never. Never. If you do, that’s an accident (read: split brain) waiting to happen.
• On the device that you run your DRBD replication over, if it uses a direct back-to-back connection to the peer node (like it should), configure a bcast link. Simple and easy to configure, and since you’re not sharing that link with anything other than DRBD replication, those broadcast packets won’t harm or …

Here is my notes on setting up replication on MySQL. In a lot of cases, that is not good enough, because it replicates EVERYTHING from the master to slave(s), whereas you may just want one or two databases replicated.

At first I thought I could just add this to /etc/my.cnf on the slave:
[mysqld]
replicate-do-db=MyDb

That didn’t work very well for statements like this, assuming you are in a database other than MyDb:

insert into MyDb.TableInMyDb values (SomeValue)

Fine, I thought, let me add this to slave’s my.cnf:

replicate-wild-do-table=MyDb.%

It’s not enough. For the Sql statement above, it still couldn’t catch and execute that statement.

After some searching and testing, I found what I was looking for. As far as I can tell, it works beautifully. All you need to do is to modify my.cnf on slave. Just add the databases to ignore in the list. Below is a …

Lenovo and Novell announce deal to preload SUSE Linux. Linspire CEO resigns. Red Hat teams with Creative Commons on Fedora content. (and more)

Lenovo and Novell To Offer Linux Preload on ThinkPad Notebooks, Novell / Lenovo (Press Release)

Fedora and Creative Commons Team Up To Deliver LiveContent Distribution, Red Hat (Press Release)

HP Drives Customer Adoption of Open Source and Linux in the Data Center, HP (Press Release)

LiMo Foundation Announces Membership Surge, LiMo Foundation, (Press Release)

…