This is probably well known issue for everyone having some MySQL experience or experience with any other SQL database. Still I see this problem in many production applications so it is worth to mention it, especially as it is connected to MySQL Performance. No it might not affect MySQL Performance per say but it limits our ability tune MySQL Performance as queries become fragile - changing execution plan leads to different query results.

So what I'm speaking about ?

Lets say you have query something like SELECT A,B,MAX(C) FROM TBL GROUP BY A - what would you expect from such query ? Column A is part of group by so its value is same for whole group. MAX(C) is also particular value for each group, while B is not part of GROUP BY and may well correspond to different values. Which one are you looking to get ? In fact this is where results becomes non-deterministic and fragile - any B from the group could be returned, …

My conversion tool is getting better, soon time to release.

What makes it possible is really that T-SQL is such a poor language, not much to take care of. But that is also the problem, since som features are far from perfect, T-SQL code contains a lot of complicated constructs, like error handling with goto's, and checking @@error efter every SQL statement. Converting that to MySQL would make horrible code, in a system where much better error handling is available. But there is currently no GOTO in MySQL...

So that will be unsolved, GOTO's and labels etc. Also references to SQL Server specific system tables, views, procedures and varibles will be largely unconverted.

Given the following scenario:

In a database, that could be represented as such:

CREATE TABLE GroupNum (
…

consulting, database, free consulting, free help, help, internet, mysql, open source, performance, SQL, technology …

Quite a while ago, I wrote a few articles on using MySQL cursors. This time, I will try and shed some light on why cursors are often unnecessary. To illustrate that, I will refactor a typical case of cursor usage.

The first part of this article explains why cursors are usually unnecessary. A few common problems with cursors are briefly discussed. Also, typical stored procedure pattern is described that uses a cursor, and a demonstration is given that shows how it can be refactored to an equivalent procedure that uses a single SQL statement instead.

In the second part of this article, the negative performance implications of using cursors are illustrated with a few benchmarks, and the cases where a cursor might be useful after all are briefly discussed.

Cursor trouble
Some time ago, I used to be quite active in some of the MySQL Forums, …

The Optimizer in 4.1 is weird. When doing joins, I expect it to figure out how to pick which tables to lookup 1st and compare in a correct manor. My expectations is a bit to much.


For instance say you have table:

• 
  
• A with 1 million rows
  
• B with 10 million rows
  
• C with 100 million rows
  

So, doing a small range on table A and taking these results to filter out with the other tables I expect the join order to be

A, B, C

Yet, the mySQL optimizer in many cases will join the table in the order of

B, A, C.

This is wrong. I know that the range generated from A is smaller then the range generated from B.

To get around this I use STRAIGHT_JOIN in a global context


SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS A.*, B.*, …

MySQL has simple but quite handy feature - slow query log, which allows you to log all queries which took over define number of seconds to execute. There is also an option to enable logging queries which do not use indexes even if they take less time (--log-queries-not-using-indexes)

Slow query log is great to spot really slow queries which are often good candidates for optimization but it has few serious problems which limits extent to which it is helpful. First - it only allows you to set slow query time in seconds, having 1 second minimum value. For most of interactive applications this is way too large - if you're developing Web application you probably want whole page to be generated less in 1 second, which issues many queries during generation. Second - if you enable option to log queries which do not use indexes it well can be flooded with fast and efficient queries, which just happen to do full table scans - for …

My thanks to Lenz Grimmer for the opportunity to present PrimeBase XT at the Hamburg MySQL September Meetup last Monday night. The meeting took place in a well equipped conference room belonging to the Chinese restaurant "Ni Hao" in Wandsbek. With over 20 attendees the venue was filled to capacity.

I must admit, I was a bit surprised (although I probably should not have been) by the highly technical nature of the questions and comments during the presentation and afterwards. As a result we had great time and spent quite a while discussing the various algorithms and implementation strategies used by XT.

I have posted the presentation as PDF on the PrimeBase XT home page. Among the topics of the presentation were: our motivation, a brief history of XT, future plans, …

People that write data integration solutions often have a tough job at hand. I can tell because I get to see all the questions and bugs that get reported.

That is the main reason I committed a first collection of 30 examples to the codebase to be included in the next GA release (2.3.1 will be released next Friday, more on that later).

Today I would like to talk about one of the more interesting examples on the de-normaliser step. What the de-normaliser step does is help you out with the lookup of key-value pairs so that you can attribute the value after lookup to a certain field. The step is a fairly recent addition to Pentaho Data Integration, but gets a lot of attention. I guess that’s because the use of a key-value pair system is often used in situations where programmers need a very flexible way of storing data in a relational database.

The …

Last night we had a number of keen souls at the Brisbane MySQL User Group. I was very impressed to see the majority of people with laptops at hand.

You can download my slides and code examples at my Articles page.

In our hands on Hackfest tutorial we created the new command SHOW USERGROUPS. Before anybody makes a comment, it was stated in the presentation that this command was made a dummy one, and is a poor candidate for two reasons.

1. The results should be more dynamic, rather then hardcoded into the source tree
2. USERGROUPS is not an ideal name due to comparisions to Users, Groups, Roles etc

Still it was productive, here was the outcome of our work for the evening.

mysql> SHOW USERGROUPS; …