In many Search/Browse applications you would see main (fact) table which contains search fields and dimension tables which contain more information about facts and which need to be joined to get query result.

If you're executing count(*) queries for such result sets MySQL will perform the join even if you use LEFT JOIN so it is not needed which slows down things considerably. In similar way MySQL generates full rows while executing queries with limit before throwing them away which makes queries with high offset values very expensive.

To get better performance you can "Help" MySQL and remove JOIN for count(*) and do JOIN after limiting result set for retrieval queries.

Lets look at following simple example with one dimension table. In real life you will usually have several of these so performance improvements can be even higher.

PLAIN TEXT SQL:

1. CREATE TABLE `fact` (
…

mysqlsla (MySQL Statement Log Analyzer) v1.5 is finally done. Some people may recall a semi-release of v1.4 a few months ago, but that version wasn’t documented or otherwise mentioned at hackmysql.com.

Now, the real next release of mysqlsla is finished, and with documentation too! (However, I have removed the guide for now; it still needs to be completely rewritten). From the old v1.3 to the present, a lot has changed due to a lot of feedback, suggestions, problems, and bugs reported from users. Essentially, the script has been completely written, but here is a brief list of the changes:

• COMPLETE redesign from v1.3: more simple to use and more informative
• Log types are now mutually exclusive (but multiple logs of same type can still be …

So, recently I returned from a long trip where I was speaking at universities in the US and Canada. It was a great trip; I met a ton of folks in the academic and business community. However, the trip left me physically drained and a bit behind on my conference planning duties (of which there are plenty). But hey, I'm back now, and the conference team is kicking into overdrive. This week, the MySQL conference program brochure was finalized, and I have finalized the entire conference session schedule.

The schedule is 100% full. That's right. Not a slot available. Chock full of techie goodness, keynote craziness, tutorials and Birds-of-a-Feather sessions. In fact, there are more technical sessions in this year's conference program than any previous year and more than OSCON, LinuxWorld, and other major OSS …

Don Seiler has published the 39th edition of Log Buffer, the weekly review of database blogs, on die Seilerwerks. As always, please see Log Buffer’s homepage to learn more about contributing. Here’s Log Buffer #39.

While this site is meant to promote Bigfix's line of network management products, they've taken a swipe at the agressive tactics of typical software salespeople with the launch of their faux "Software Truth" site.  In what purports to be a congressional investigation with subpeona'd sales reps squirming under bright lights you hear a sales rep confess: "The truth is we don't have flexibility...it's like a corpse after 6 hours of rigor mortis has set in."

• BigFix: SoftwareTruth, Press Conference, …

In a few weeks I'm headed out to my 4th MySQL Conference. Typically I get all my travel/lodging set up way in advance but this year is different and I just got the last piece into place today. Yikes!

I haven't had much time this year for pre-conference MySQL posts, which tend to get more frequent leading up to the conference. I guess there is still two full weeks to get back into groove before I head out.

I'm looking forward to the conference for a few reasons (in no particular order):

• 
  
• Presentations: I'm excited for a number of presentations on the schedule. As I look back over the years I think the thing I've enjoyed the most in presentations is hearing how other folks are using MySQL to solve their data storage/access problems. My first conference in (Orlando, 2004) very heavily featured the work …

We've been working on automatic master promotion for MySQL using lbpool and I'm reminded of a few ideas we had after reading about MMM.

The MySQL Master-Master replication (often in active-passive mode) is popular pattern used by many companies using MySQL for scale out. Most of the companies would have some internal scripts to handle things as automatic fallback and slave cloning but no Open Source solution was made available.

Few months ago we were asked to implement such solution for one of the customers and they kindly agreed to let us release things under GPL2 License, and we gave them reduced rate for being Open Source friendly.

We actually considered a dual master approach but abandoned because it was non ideal. …

We’ve been working on automatic master promotion for MySQL using lbpool and I’m reminded of a few ideas we had after reading about MMM.

The MySQL Master-Master replication (often in active-passive mode) is popular pattern used by many companies using MySQL for scale out. Most of the companies would have some internal scripts to handle things as automatic fallback and slave cloning but no Open Source solution was made available.

Few months ago we were asked to implement such solution for one of the customers and they kindly agreed to let us release things under GPL2 License, and we gave them reduced rate for being Open Source friendly.

We actually considered a dual master approach but abandoned because it was non …

I've just released three updates to tools in MySQL Toolkit. The updated programs are MySQL Query Profiler (major new features and helper script), MySQL Table Sync (bug fixes, documentation, features), and MySQL Table Checksum (minor sanity check enhancement).

So, I’s been a long time since I wrote my last post here. Lots of work and almost no new interesting technologies in my work caused such delay. But today I’m proud to announce release of really interesting project: MySQL Master-Master replication manager - set of flexible scripts for management different MySQL deployment schemes with master-master replication involved.

More information about this software could be found in detailed announce in Peter Zaitsev blog (actually this software was created by me for his company’s client) or at project page. All your questions and suggestions welcomed in …