I’ve just improved the MySQL duplicate index checker I whipped together a few days ago. As I guessed, my hasty coding left some things to be desired. I’ve fixed some bugs, added support for finding duplicate foreign keys, and switched to a command-line parsing library that comes standard with Perl, so it’s more convenient to run without needing to fetch modules from CPAN. You can download it from the original article.

This is another bugfix release with only a couple of module-related new features.

The news for this release are:

* “fixed a bug with using the alternate search backend. database search was still done while it shouldn’t.”

* MFH: addon.php allows for addon scripts that are implemented using a module

* Added extra hook ‘posting_custom_action’ to the posting editor code

* MFH: Added signing of editor form data to prevent data tampering

* Added a simple general purpose private key based signature system

* redirect back to vroot if mark-read is used (fixing #403)

For users running 5.1.15 we recommend to replace at least search.php to lessen the load on searching. A couple of smaller fixes were also implemented therefore we recommend every user running phorum 5.1.x to upgrade to this version.

If you want to see the full changelog you can find it here …

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, …