Shoot in the foot

I've just finished reading two recent blog posts about new query optimizations in the upcoming MySQL 6.0, it's all fine and dandy but ...
Looking at Correlated semi-join subqueries and PostgreSQL by S. Petrunia we can read something like

Quote:

The first thing we did was to take a look at PostgreSQL as it is easily available and seems to have at least decent subquery handling (or even better than decent, I have not heard much complaints).

Does this mean their benchmark is PostgreSQL? I mean, Oracle and SQLServer are easily available too ... not to mention the comparison any reader can see a few lines below between MySQL's and PostgreSQL's explain plans.

The other amusing read is New optimizer features in MySQL 6.0 again by Sergey where you can find an interesting speed comparison which (might) boil down to

Server version Wallclock time # of reads
MySQL 5.1 12 min 9,001,055
MySQL 5.2 1.8 sec 153,008
MySQL 5.2 no_semijoin 25 sec 7,651,215
PostgreSQL 8.2.5 0.1 sec 2,413

Very interesting!

But anyone interested in query optimization should take the time to check the whole paper, extremely informative (and much of it's content is good for any database too)!!!

Ok, now LET THE FLAME WAR START ;-)

BTW MySQL 5.1 is not in production currently, while PostgreSQL 8.2.5 has just been superseded by the new PostgreSQL 8.3.1

Powerline, the good, the bad and the ugly ...

After much esitance I decided to jump on the bandwagon of Powerline (a.k.a. Homeplug), that's it, ethernet over power!
At first it sounds great, I mean, no cabling, no pesky radiations going round like with WiFi and so on ... an easy way of sharing internet connection for my 2 PCs at home.
I tested WiFi 802.11b before and wasn't impressed with it's performance, so I reached my purse and got a pair of Powerline adaptors, plugged them in and ... voilà, instant networking [the good].
This looked extremely good, easier than WiFi, but after some usage I noticed that it was a quite slow connection, so, unfortunately, I took a peak at the manual and discovered that max theoretical (including channel control data) speed is 85Mbit/s, while effective maximum network speed can't exceed 25Mbit/s :-( [the bad (1)] and that you need to install more software/drivers in order to add security to your network, much like WEP for WiFi, which adds to complication and is likely to subtract network speed [the bad (2)].
Network speed was unimpressive, so I run a quick test with JGaa's NetCPS and here are the results for you to read, as seen from both sides

C:\>netcps -s

NetCPS 1.0 - Entering server mode. Press ^C to quit

Waiting for new connection...

Client connected from 192.168.1.9

---> CPS   1519616.00  KPS:  1484.00  MPS: 1.45

Avrg CPS   1515546.00  KPS:  1480.03  MPS: 1.45

Peek CPS   1542144.00  KPS:  1506.00  MPS: 1.47

Client disconnected. 104857600 Kb transferred in 69.19 seconds.

C:\>netcps 192.168.1.5

NetCPS 1.0 - Entering client mode. Press ^C to quit

Connecting to 192.168.1.5 port 4455... Connected!

---> CPS   1518097.88   KPS:  1482.52  MPS: 1.45

Avrg CPS   1515524.13  KPS:  1480.00  MPS: 1.45

Peek CPS   1553373.25  KPS:  1516.97  MPS: 1.48

Done. 104857600 Kb transferred in 69.19 seconds.

I'd not say very fast ...
You read about [the good], [the bad] and here is [the ugly]



(when I'll be able to retrieve a photo from my mobile) Ok, I've made it

the plug is very light, but quite big, or at least bigger than what I expected.
After all this could seem a bashing post, so I'm adding the results of the same test under 802.11b

C:\>netcps -s

NetCPS 1.0 - Entering server mode. Press ^C to quit

Waiting for new connection...

Client connected from 192.168.1.10

---> CPS    495616.00  KPS:   484.00  MPS: 0.47

Avrg CPS    487532.91  KPS:   476.11  MPS: 0.46

Peek CPS    526336.00  KPS:   514.00  MPS: 0.50

Client disconnected. 104857600 Kb transferred in 215.08 seconds.

As you can see homeplug is much faster than 802.11b, don't have a more recent 802.11x device to compare :-(

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.