About every second application I look at has some tables which have redundant or duplicate indexes so its the time to speak about these a bit.

So what is duplicate index ? This is when table has multiple indexes defined on the same columns. Sometimes it is indexes with different names, sometimes it is different keywords used to define the index. For example it is quite frequite to see something like
PRIMARY KEY(id), UNIQUE KEY id(id), KEY id2(id)

The logic I heard behind this often - create primary key as object identifier, now we create UNIQUE because we want it to be UNIQUE and we create KEY so it it can be used in the queries. This is wrong and hurts MySQL Performance. It is enough to create PRIMARY KEY and it will enforce unique values and will be used in the queries.

The other case is simply having multiple keys on same column(s) - I guess someone thought key would make sense while did not …

SciBit is proud to announce the release of MyReporter CGI and ISAPI for Win32 platforms.

Changes:
* Fix: IIS ISAPI relative directory issue. The CGI and ISAPI behaviors are now identical and all templates can be positioned relative to the MyReporter directory.
* Enhanced: Faster emailing facilities
* Add: Emailing now allows for display of the report or redirection to a URI of your choice
* Add: Support for the newest MyCon 2.10 report engine

For more information. examples and downloads see: MyReporter

For previous releases, see:
MyReporter v3.0

While Web 2.0 is probably at least as hard to define as pornography (e.g. I know it when I see it) there's no doubt that it's growing rapidly.  By our estimates, more than 90% of all Web 2.0 sites are powered by MySQL including the likes of media darlings like YouTube, FaceBook, Digg, Wikipedia, Cyworld, Flickr and most of the social networking sites.  In fact, if you add in all of the sites using MySQL it's probably over a billion page views per day and doubling every six months. 

And if you don't like Tim O'Reilly's definition of Web 2.0, then you can …

automation, best practices, database, internet, technology, tips web

http://www.artfulsoftware.com has a “Common Queries” page, which I find rather useful. I hadn’t realized its history, as described on the homepage:

Our collection of common MySQL queries outgrew Chapter 9, and is still growing, so we turned it into a PHP page driven from a MySQL table.

We’ve released new bits for the beta of solidDB for MySQL. We now have a version compiled for 64 bit Linux. Check it out at http://dev.soliddb.com/download/

The posts I've been reading and writing recently have reminded me how Object-Relational Mapping (ORM) systems make it fun and convenient to interact with databases. For some of the reasons they're a developer's favorite, they can be a database administrator's nightmare (think surrogate keys). But designing tables with a consistent set of columns has its benefits. Just because the columns are meta-data that have no intrinsic meaning doesn't mean they have no value. In this series of articles I'll show you several ways to use such "meaningless" meta-data to enable powerful, efficient application-level role-based access control (RBAC) in the database, with a focus on web applications, though you could do this for any application.

The systems I've built are complex, so I'll split this into at least two articles. This first article will discuss other privilege systems I've seen in web applications, including …

There's a new blog in town by MySQL consulting guru Nitin Borwankar.  Nitin's working on a few super secret startups and invariably they are using a lot of open source technology to create sophisticated tagging systems that create "folksonomies" or tagging systems that are developed in a collaborative fashion.  Sounds easy, but how do you map that in and out of standard MySQL?  Ahh... that's the question.  And Nitin has some practical answers borrowing from data warehousing techniques that help illuminate the way.  You may not always agree with how Nitin's does it, but its thought provoking …

Vim 7.0 introduces tabs to Vim. I wasn’t aware of this until BigE pointed it out to me, and to this tip; that lets you move around the tabs with firefox style short-cuts for tab navigation. That’s a great tip, but what about us OS X users who prefer using the “apple/command” key? I decided to use Safari style tab navigation for vim, as some of the would have caused problems with default keybindings. You can see the details here.. For people wondering how to get Vim 7.0 for their Macs, MacVim.org is your best bet.

Tags: php, vim, mysql, …

The VMWare Server allows to do great things that you usually wouldn't like to do on your working machines, mostly because of the danger to damage something that can take a lot of time to recover. And it's also a great thing to play with advanced MySQL issues.

So I installed myself a "MySQL playground" - a VMWare machine running SuSE Linux 10.1 - and installed three MySQL 5.1.11 servers (placed in different directories and assigned the ports 3306, 3307 and 3308). The first thing that I played with was to set up a multimaster replication example, based on Giuseppe Maxia's article: Advanced MySQL Replication Techniques (I planned to do this for quite a while and now I finally did it!).

…

I did a bunch of housekeeping in the MySQL Cluster documentation today.

Confession: I’m the bright light who came up with the term “SQL node” because I didn’t like “API node”. At the time, it seemed like a good idea, but as time has gone on, I’ve come to see the error of my ways. However, lots of people now use the term, so I guess it’s not a good idea to change it out from under them. So here’s what I’ve decided:

API node - Any application that accesses Cluster data. Basically this means any NDB API application.

SQL node - A subspecies of API node that provides an SQL interface to a Cluster. Basically, this means a MySQL Server that’s part of a Cluster. (mysqld itself isn’t an NDB API application, but the bits that let it talk to a Cluster use the NDB API.)

Also, ndbd processes were in the distant past referred referred …