Terminally Incoherent

I like to let MySQL generate unique keys for most of my tables by making them numerical and using the AUTO_INCREMENT feature. It is both an easy and convenient way to deal with primary keys. It has one disadvantage though.

Since the key is generated upon insertion of a new row, you can never know its value before the SQL statement is executed. Furthermore, it can be problematic to insert a new row into a table, and then use its key as a foreign key in another table. One could potentially follow the insert statement with a selection, but unfortunately it is possible that the primary key is the only unique value that distinguishes several rows. We can never be a 100% sure that we are getting the right one, using a selection statement.

MySQL provides us with a nifty feature that does precisely what we need:

SELECT LAST_INSERT_ID()

Lets say we have two tables tab1(id, value) and tab2(id, value, tab1_id). To insert a new row to tab1, and then new row to tab2 while referencing tab1 we can do this:

INSERT INTO tab1 (name) VALUES ('some value')
INSERT INTO tab2 (value, tab1_id) VALUES ('another value', LAST_INSERT_ID())

If you are working with PHP, this whole thing becomes even easier. The mysql module contains a great function that lets you get the id of the last inserted row:

$last_inserted_row = mysql_insert_id($dblink)

Here, $dblink is a database connection identifier variable (the one returned by mysql_connect statement). The returned key is found based on your unique connection, you can safely assume you are getting the correct value, even in a multi-user server environment. Other users, connecting to the db at the same time will get last unique id’s that match their connection.

I found this list today via Homo-Adminus, and I think it’s really worth sharing: 84 MySQL performance tips.

Some of these are common sense (eg. index stuff, but don’t index everything), while other are little nuggets of awesome DBA knowledge that only come with experience and expertise.

It’s something you should definitely check out if you work with MySQL.

I have a table with some dated records. I wanted to do some weekly reports on this data. MySQL has a nifty function dubbed WEEK() which will return a week number. It allows you to break your data into week long intervals very easily. For example, the following query will tell me how many records came in each week:

SELECT COUNT(*), WEEK(mydate)
FROM mytable
GROUP BY WEEK(mydate)

The problem here is, that the output is totally meaningless to me as I do not have a clue what does week 36 mean. What I really want is to have on the screen is a nice, human readable date interval - the beginning and ending date of any given week.

Surprisingly, this turns out the be a major pain in the ass. There is no simple function that will yield a week interval (or start/end date of a week) given a week number. You have to find these dates manually. Here is how I did it:

SELECT
    COUNT(*) AS reports_in_week,
    DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY),
    DATE_ADD(mydate, INTERVAL(7-DAYOFWEEK(mydate)) DAY)
FROM
    mytable
GROUP BY
    WEEK(mydate)

How does it work? The DAYOFWEEK() function returns an integer ranging from 1 (Sunday) to 7 (Saturday). So if mydate happens to be Tuesday we get the following statements:

DATE_ADD(mydate, INTERVAL -2 DAY)

which essentially means “subtract 2 days from mydate (which is that week’s Sunday) and also:

DATE_ADD(mydate, INTERVAL 4 DAY)

which yields the date of that week’s Friday.

One would think that there would be a function to accomplish this automatically, but alas there is none. I think this is as simple as it gets. I hope this helps someone, because it took me quite a while to figure this out.

I needed to quickly dump bunch of MySQL tables into Excel so that our directors can play around with the data. It does make sense, believe me. It would take me at least a day or two to implement, test, and integrate the statistical analysis they want to do on the data into our web application. Meanwhile they can perform the same calculations on an excel sheet in the next 15 minutes. So I get to add bunch of items to my todo list, and they get bunch of data to play around with. Everyone wins

And yes, they specifically asked for Excel format, and the future reports must have “export to Excel” feature.

Anyways, here is why I love MySQL:

SELECT * INTO OUTFILE 'result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM my_table;

You get a CSV file formated nicely for Excel. Now you can just open it, and save it as xls file if that is what you need.

If your table is to big for Excel you can of course use a WHY clause to cut down the number of results.

Mysql does not support MINUS operator which is not the end of the world. For most queries you can really care less if it’s implemented or not. My web app never needs to use it to function properly, which makes MySQL a perfect choice here. But, sometimes human beings are more demanding than simple php applications. Especially if you want to extract some non-obvious information from the database.

Just today I wanted to see who in our company does not have an assigned laptop. I have two tables - one is user table which contains names and info of all the employees. The other one is computer table which contains service tags of all the machines we own. You can join these two on username and you figure out which computer is assigned to who. But I wanted the opposite thing - which is a logical intersection of the two tables on username. Which is usually obtained via MINUS operator.

Of course MySQL does not have MINUS, so I spent 5 minutes staring at the screen and typing mangled SQL queries trying to extract this info. Then I decided to google for it. Here is the solution:


SELECT users.username
FROM users
LEFT JOIN computers ON computers.username = user.username
WHERE computers.username IS NULL


Duh! I feel dumb that I couldn’t figure this out myself, but I was nowhere near the solution. Anyways it’s good to know for the future.