Firebird's explain PLAN

After a post on Devshed forums I'm putting here an excerpt from a little tutorial about Firebird I wrote some time ago, this is about the explain plan of a query:

More on speeding … the Explain plan

An extremely useful feature of relational databases is the “explain plan”,which
shows us how the database engine is going to execute a query. It allows the DBA to
check if indexes are used and how tables are queried. One of the most effective ways of
using it is to use the Firebird option “PLANONLY” which means that the query to be
analyzed is submitted to the server and the plan retrieved, but the query is not executed!

Note that this is also an effective way of checking the SQL syntax of a statement, much
like Oracle’s parse statement.

The planonly option can be set to ON or OFF at your option, remember to turn it
off to obtain results for your queries.

The plan is accessed through ISQL this way:

>isql
SQL> CONNECT “C:\firedata\first_db.fdb” user ‘SYSDBA’ password ‘guess_me’;
…
SQL> SET PLANONLY ON;
SQL> SELECT a.emp_no, b.currency from employee a inner join country b on
a.job_country = b.country;

After submitting the query we retrieve the plan, which shows usage of the primary key of
table country:

PLAN JOIN (A NATURAL,B INDEX (RDB$PRIMARY1))
SQL> SET PLANONLY OFF;

This way we can check the execution of more complex queries and find areas of
improvement

You can see an example of it's usage here.

Audacity 1.3.2 is coming

See release notes for Audacity 1.3.2

Handy MySQL function

Here is a simple function that can help coping with wrong database design, when you have a mix of NULLs and blank fields meaning the same, no value, which they shouldn't.

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`is_initial` $$
CREATE FUNCTION `test`.`is_initial` (f varchar(255)) RETURNS BOOL
BEGIN
 
 
SET @is_initial = false;
 
CASE f
WHEN NULL THEN SET @is_initial = TRUE;
WHEN '' THEN SET @is_initial = TRUE;
ELSE SET @is_initial = FALSE;
END CASE;
 
RETURN @is_initial;
 
 
END $$
 
DELIMITER ;