Hooray for Data Structures… Where Are They?

The Key-Value Pair is my favorite programming tool. Most SQL type databases lack this feature in it simplest form. Some would argue, “Well that’s because databases don’t need dah-blah-dee-blah-blah…” Well, if they don’t need it I don’t need to write this post. :p

How many times have you found yourself asking “Why isn’t there at least simple array support!?” When you don’t want the overhead of creating a table, or a temporary table, simple data structures really do come in handy (hint, hint, nudge, nudge to all the companies producing database software with stored routine support).

I’m not one to let an apparent lack of functionality ruin my day. Instead, I add it. I had a great computer science professor who said “You don’t need pointers to create a linked list.” Then he proved it. You can create, pretty much, any abstract data structure with …

Why A Table of Dates?

I thought it would be nice to build on the numbers table with another very useful tool.

Dates are pretty important–especially if you are playing with data warehousing. Not only dates, but all the different properties and derived goodies contained within them. You really don’t need a dates table, unless you care about efficiency and getting to all the good stuff quickly. Perhaps I’ll showcase some of the “goodies” in later posts.

Sure, most databases have built-in functions to get everything you might want. But do you really want all the inefficiency that comes with calling functions and doing calculations over and over again? I hope not. I used the method in this article when I developed a SQL Server data warehouse for a company I worked for. It was adopted by our other SQL Server guru for all the date-sensitive stuff we did (which was a lot). It increased efficiency by leaps and …

Making The Numbers Table Useful

It’s not easy to find a solution to a very simple problem in MySQL: converting a comma separated list of values into rows. Oracle database users find gobs of tutorials on using REGEXP and CONNECT BY LEVEL to make this happen. MySQL doesn’t have that. So, use the numbers table from the previous post!

The transposing is made possible by (ab)using the SUBSTRING_INDEX function. I love this function. It is right up there with GROUP_CONCAT when mixing NULL and non-null strings.

I will be using comma separated values. You can use any delimiter character you like.

Bonus: This works with empty strings, strings with one value only, and empty delimiters (i.e. “my value,,previous is empty”). No extra code needed.

Making Magic Happen

SET @mycsv = …

Why You Care About Numbers

I have worked a bit with Oracle. As such, I hang out around Oracle developers. There seems to be a common complaint among our kind when it comes to MySQL–”There aren’t any sequences!”

It never really bothered me. I wasn’t always an Oracle guy, so I didn’t always have sequences. I’m the kind of person who likes to experiment and make things happen. It just so happens there is a nice tool to help with this perceived absence: the “numbers table.”

It is really easy to set up. And, regardless of your database background, I think you will grow to love your utility.

There are multiple ways to achieve the result you want. For me, the easiest way is to work with decimal numbers. Why? Because that’s how we think. That’s pretty much standard for humans. Yeah, I know. Geeks think hexadecimal. Let’s not go there.

Without further ado, here is how you can create …

TEXT fields are a nightmare. For you and for your server. It is slow to retrieve, and if you are doing searches on it, be prepared, things are going to get bumpy.

If you use MySQL with a MyISAM engine, this may not be an issue for you, you can create a FULLTEXT index, your only problem is if you want to add a new column, an alter table can take forever, since MySQL creates a new table and copies the old data to the new table. For those who uses MySQL with an InnoDB engine, prepare because, you’ll have more issues. Indexes can’t be FULLTEXT and if you do need an index you must inform the length of it. It defeats the purpose of you doing the search in that field.

The observation above is only true for MySQL 5.5 or below, since version 5.6 MySQL does support FULLTEXT indexes on InnoDB – thanks Davey …

I am potentially one of the least technical people in my generation. I’m 30 and I am afraid of my cellphone, my laptop, Netflix, the microwave…. Okay, afraid is maybe a strong word, but baffled by them at the very least.

In high school, while my classmates wrote most of their papers and assignments on the computer, I insisted on writing everything out by hand and only typed it out afterwards if absolutely required. It wasn’t that I had issues with typing – my mom who worked as an administrator for many years made sure that I learned to type from a very young age and I type quickly with a reasonable amount of accuracy. I just felt that writing by hand kept me more “connected” to the words I penned. Simply, my name is Sarah and I am a Luddite.

After high school I studied journalism for a couple of years and then entered the workforce into a number of different jobs, such as in sales and marketing and it became necessary …

 If table is partitioned then that makes it easy to maintain. Table has grown so huge and the backups are just keep running long then probably you need to think of archival or purge.

Purge the data if you don't want data from old partitions just by doing truncate or drop of those partitions which completes momentarily without locking the table for a long time

Archival can be done couple of ways.
We can take mysqldump (preferably from a slave) with a where condition to filter out the data you don't want to copy. And then import the dump file to archive database. check this link out for mysqldump with where clause .. and once data is copied, the …

The MySQL Utilities team is happy to introduce a new MySQL utility named  ‘mysqlbinlogpurge‘, which allows users to easily and safely purge binary logs on a master by determining the binary logs that are obsolete. This utility is included in MySQL Utilities
release-1.6.1 Alpha release along with ‘mysqlslavetrx‘ and ‘mysqlbinlogrotate‘. This utility enables you to purge binary logs by ensuring that any files which are in use or required by any of the slaves in a replication topology are not deleted. This is achieved by
checking which binary logs have been read on each slave. This determines the minimal set of binary log files that …

Today Oracle released MySQL 5.7.6 milestone 16. With this, MySQL 5.7 has been in development for over 2 years.
Compared to MySQL 5.6, the changes are quite extensive. The main effort of the team has been focused on speed, with performance reportedly improved from 2 to 3 times compared to previous releases.
A full list of what is new would take too much space here, but I would like to mention some key points:

• Oracle has spent a considerable amount of energy in the improvement of MySQL security and safety. You will see many new features, but even more old features that were deprecated and more that were removed after deprecation in 5.6.
• The installation process has been changing in every …

The MySQL Utilities Team is pleased to announce a new alpha release of MySQL Utilities. This release includes a number of improvements for useabilty, stability, and a few enhancements. A complete list of all improvements can be found in our release notes.

New Enhancements!
This release represents a stable release of the product. Along with several defect patches, we also include the following enhancements.

Improved support for MySQL 5.7 early releases
Improved output for mysqldbcompare
Improved SSL support

New Utilities!
This release also has three new utilities for you to try out:

mysqlslavetrx - skip transactions on one or more slaves to solve errant transactions and consistency errors - …