Views are a great feature which is available as of MySQL 5. A view can be seen as a named and persistently stored SELECT expression. After defining the view it can be referenced just like a table in other queries.

Metadata for MySQL Views can be queried using the TABLES and VIEWS tables from the information_schema. This can be very helpful when maintaining or developing views.

However, this metadata would be even more valueable if it would provide …

I was writing this in an email to a co-worker today, could possibly interest people in the outside world as well. It’s a good idea to look at the source at the same time as reading this :)

In ha_ndbcluster::write_row(byte*),

if (table_share->primary_key != MAX_KEY)
{
/*
* Increase any auto_incremented primary key
*/
if (has_auto_increment)
{
THD *thd= table->in_use;

m_skip_auto_increment= FALSE;
update_auto_increment();
/* Ensure that handler is always called for auto_increment values */
thd->next_insert_id= 0;
m_skip_auto_increment= !auto_increment_column_changed;
}
}

We set next_insert_id to 0 so that in handler::update_auto_increment() we end calling the handler and never doing it just inside the server.

The handler function that we end up in is: …

In sqlcmd, you can connect to a different server by this command:

:connect MyServer

The above command will attempt to use integrated / Windows Active Directory authentication. To use traditional Sql Server authentication, use this instead:

:connect MyServer -U MyLogin

You can do the same kind of thing in Oracle Sql Plus by using this command:

conn[ect] MyLogin@OracleSID

For mysql command line, use this:

connect -h MyServer -u MyLogin -p

That is assuming that you are allowed to login to the MySql server from whereever you are now.

DUBLIN, Ireland -- MySQL AB is seeing increased growth and momentum in EMEA, resulting in further expansion of its European corporate sales office in Dublin, Ireland. This is a result of unprecedented demand for MySQL database software in Europe. Recent major European customer wins for MySQL include Afnor, Alcatel, neckermann.de, Nokia, Pricer, Sagem Monetel, SEAT Pagine Gialle, Shunra, United Nations' FAO, Virgin Mobile, Waitrose, and Ya.com.

InnoDB is a special case among MySQL storage engines because they have clustered indexes, which means surrogate keys have to be treated differently in InnoDB. This article gives a quick overview of clustered indexes, and explains why they make it even more important to do careful analysis before making decisions about surrogate keys on InnoDB tables. Overview of clustered indexes in MySQL A clustered index is just like any other index, except the index holds the data itself, in index order.

For those of you who want to hear me read this instead of having to do so yourself (no hyperlinks), click here for .ogg and here for .mp3.

I migrated the blogs’ database and code from moonunit to avenger, both of which run Debian Sarge. Y’all should now notice a vast improvement in throughput. The ISP that Moonunit sits on should also see a large decrease in traffic.

Today, I added a campaign to raise funds for hosting. If you have any extra funds, please help me with colocation costs. It’s expensive and y’all use up a bunch of bandwidth. If you find this site …

Let’s look into selectivity, as this is an important topics when looking at index performance. (Oooh, I said “performance”, watch everyone’s ears perk up!).

This will probably answer the questions “Why isn’t MySQL using my index?” or “Why is my query so slow when I have an index on it?”

Selectivity describes how different values of a field are. It is a number from 0-1, although you can also think of it as a percentage. A value of 1, or 100%, means that each value in the field is unique. This happens with UNIQUE and PRIMARY keys, although non-unique fields may have a selectivity of 1 — for example, a timestamp value in a not-often-used table.

To calculate this, you take the total number of DISTINCT records and divide by the total number of records.

My company has a large Users table, so I grabbed some statistics off of that:


+----------+
| …

Yet again, the Boston MySQL Meetup was a success. Larry Stefonic, Senior Vice President, Worldwide OEM & Embedded Sales and President, MySQL K.K. (Japan) was on hand to witness the event. There were 15 people total at the meetup, which is our lowest yet, but I’ll get to why I was not disappointed at all later on.

However, the topic was advanced: “Measuring MySQL Server Performance for the Sensor Data Stream Processing” presented by faithful MySQL Meetup attendee Jacob Nikom, from MIT Lincoln Labs. (he’s been to every meetup!)

And I was very impressed with the questions folks asked of Jacob — the group was half the size of what we usually are, but we were all fully engaged. I’m very proud of the MySQL group! We have a diverse range of skills and I’m glad we can accomodate all of them.

Next month’s topic will be “Storing images in a database,” which was pushed out of the way in April to make room for Jay …

riteshn asked me about books I would recommend people to read to learn more about database design. This list is not all that original, but when I find that I am looking for a reference book these are the books I constantly return to:

Readings in Database Systems : Fourth Edition
Database Systems: The Complete Book
…

All -

Thanks to all of you for the kind words regarding one of the presentations I gave at the recent User’s Conference - “MySQL In-Depth for DBAs”. For those of you requesting the PPT deck, you can now download it from O’Reilly.

–Robin