Category: MySQL

MySQL related posts

SQL graphics

MySQL

SQL is not meant to generate graphics, for sure; but I see some cases where generating non-tabular output can be desirable, as I will show in future posts.

I’d like to explain the basics of working SQL graphics: it is actually possible to do whatever you like. How?

Coordinates system

We’ll now develop a coordinates system using SQL. By producing this, I will have proven my point that anything is possible, and will provide an additional proof of concept.

To start with generating coordinates, I’ll need a helper table: a numbers table (tinyint_asc, example, with numbers ranging 0..255).

We’ll strive to produce a 10×10 coordinate matrix. To do this, we’ll self-join the numbers table against itself, and use a helper variable to set the size of the matrix. Continue reading » “SQL graphics”

“Vote for me…” how to embed in WordPress

MySQL

[Clarification: I’m not actually asking you to vote for me :D, the title just follows a previous post]

Diego Medina has published a JavaScript code that can be embedded in your blog posts, and which allows for voting on Planet MySQL from within your blog.

Shared below is how to set this up for WordPress users. This is not a WordPress plugin, mind you. You’ll need to manually edit the WordPress template files (can be done from the Dashboard->Appearance->Editor->Single post).

The page you’re likely to edit is single.php, but depending on your template this can change. The explanation below assumes a single post page. This can also be worked out for your blog’s home page, which lists several entries.

Since there is no point in presenting the Planet MySQL voting widget for entries which do not relate to MySQL, the code verifies that the post is in the ‘MySQL’ category. You need to change this if your categorization differs. Mind that the category’s name is case sensitive. Continue reading » ““Vote for me…” how to embed in WordPress”

Reasons to use InnoDB Plugin

MySQL

I wish to present some compelling reasons to use the InnoDB plugin. The plugin is a drop-in replacement for “normal” InnoDB tables; enabling many new features. It is the outcome of a long termed silence from InnoBase (Oracle), which were thought to be neglecting the InnoDB engine.

I’m going to leave out “performance” for the reason that grander forces have benchmarked and written about it.

Compression

Using the new Barracuda table format, table data can be compressed. Compression depends on the type of data you have in your table, and in KEY_BLOCK_SIZE. I have found tables with lots of textual data to compress well, to about 25% volume (that is, reduction of 75%), and strictly integer-typed tables (like an a-2-b connecting table) to compress poorly.

I have seen an InnoDB 50GB database shrink into some 12GB only. Wow! That meant a server which only had RAID 1 two 72GB disks, and which was dangerously filled up with disk space, could now accommodate the database, a backup, and then some!

Continue reading » “Reasons to use InnoDB Plugin”

Auto scaling, scaled SQL graphs concluded

MySQL

I wasn’t sure I was to go this far. After catching breath the following have been added to Generic, auto scaling, scaled SQL graphs, and these will conclude my current hacks:

  • Displaying X-axis min/max values.
  • Support for Y-axis values precision.
  • Support for pre-defined scale range.

The addition of the above makes for presentable, usable graphs. See also sample graphs at the end of this post.

Step 8: adding X-axis values

I add minimum/maximum X-scale values to the graph. What was just ordering_column before, now turns to be the x in the y = f(x) function. Continue reading » “Auto scaling, scaled SQL graphs concluded”

Generic, auto scaling, scaled SQL graphs

MySQL

In Rotating SQL graphs horizontally, I have shown how to rotate an ASCII SQL graph into horizontal position.

I was dissatisfied with some parts of the solution, which I will show now how to fix:

  • I had to manually scale the graph values so as to fit nicely into screen.
  • I had to rely on hard coded scaling schemes.
  • I had to rely on hard coded column names.
  • I had no y-axis legend.

I will now present an SQL query which allows for pluggable queries, which creates self, auto scaling graphs, along with y-axis scales.

Using deeply nested subqueries, we will evolve a simple SELECT query into an elaborate graph. I will present the many steps required, followed by explanations and sample results. But in the end – the steps are unimportant. I’ll present a generic query, into which your own SELECT can be embedded, and which will provide you with the graph.

We’ll use the same example, found in graph.sql.

Continue reading » “Generic, auto scaling, scaled SQL graphs”

Rotating SQL graphs horizontally

MySQL

We all love graphs. We all love SQL hacks. We all know the SQL hack which displays a character-based graph (example follows for those unfamiliar).

But we all love horizontal graphs, not vertical ones. We are used to the X axis being horizontal, Y being vertical. Not vice versa.

In this post I’ll present a SQL hack which rotates a vertical graph to horizontal. In fact, the technique shown will rotate any ‘textual image’; but graphs are a nice example.

A vertical graph example

What’s prettier than a sinus curve? I have prepared a simple table that will serve nicely, and can be found in graph_tables.sql.

Continue reading » “Rotating SQL graphs horizontally”

The DB problem inherent to dynamic web pages

MySQL

When building web sites, a popular demand is a maximum page load time.

For example, many would require < 0.5 seconds (or even less) for major pages loading time. Of course, there are numerous factors for page load time: network, caching, web servers, scripting language/code, database access and more.

Naturally I want to discuss the use of database access when creating web pages. I’ll be referring to dynamic web pages, such that are created by common languages as PHP, Java/J2EE, Ruby, ASP(.NET) etc.

A very common programming style is – what’s called in the Java jargon – using “scriptlets” as in the following JSP page:

<html>
<body>
    Time now is <%= new java.util.Date() %>
</body>
</html>

The above replaces the “<%= new java.util.Date() %>” part with a text representation of the current time.

If I were to produce a dynamic content site, say, a WordPress blog, like the one you’re reading, I would need to generate several dynamic contents: the latest posts, the popular tags, the comments for this post, etc. These are generated by calling upon the database and running some queries. I suppose there’s nothing new in what I’ve explained so far.

The problem

When generating a “heavyweight” page, like some online newspaper or bookstore, there may be many queries involved. Are you logged in? Do we have recommendations for you? What are the latest topics? What have you been interested in before? Do you have friends online? What content have you produced on the website?

Continue reading » “The DB problem inherent to dynamic web pages”

Character sets: latin1 vs. ascii

MySQL

Unless specified otherwise, latin1 is the default character set in MySQL.

What I usually find in schemes are columns which are either utf8 or latin1. The utf8 columns being those which need to contain multilingual characters (user names, addresses, articles etc.), and latin1 column being all the rest (passwords, digests, email addresses, hard-coded values etc.)

I find latin1 to be improper for such purposes and suggest that ascii be used instead. The reason being that latin1 implies a European text (with swedish collation). It is unclear for an outsider, when finding a latin1 column, whether it should actually contain West European characters, or is it just being used for ascii text, utilizing the fact that a character in latin1 only requires 1 byte of storage. Continue reading » “Character sets: latin1 vs. ascii”

New and Noteworthy in openark kit

MySQL

A new release of openark kit is out. Some interesting additions.changes are:

openark kit is a set of utilities for MySQL, helping in easing out everyday’s work. Let’s look more closely at the changes.

oak-online-alter-table

The utility allows for non-blocking ALTER TABLE operations, under certain limitations. One limitation which has been removed in the current release was the single-column UNIQUE KEY limitation. As of now, a requirement for running oak-online-alter-table is that the altered table has some UNIQUE KEY. It could be numerical, textual, single column, multi-column (compound), anything. Continue reading » “New and Noteworthy in openark kit”

Unwalking a string with GROUP_CONCAT

MySQL

“Walking a string” is an SQL technique to convert a single value into multiple rows result set. For example, walking the string ‘hello’ results with 5 rows, each of which contains a single character from the text.

I’ll present a brief example of walking a string, and then show how to “unwalk” the string: do the reverse operation.

To walk a string, an integers table is required (or this could be a good use for SeqEngine): Continue reading » “Unwalking a string with GROUP_CONCAT”