maria-developers team mailing list archive

[Lars Rönnbäck <lars@xxxxxxxxxxx>]

When doing some comparisons of query optimization in Microsoft SQL
Server and MariaDB I noticed that there is a second type of table
elimination which MariaDB has not yet implemented. It depends on 
foreign
keys being declared, and can under some circumstances substantially
improve performance.

Rather than to write a lengthy discussion, here is a test script for
MariaDB 5.2.5 in which the queries with the condition "where ... is not
null" should result in this type of table elimination:
http://pastebin.com/s68RwgG2

This screencast shows the table elimination in effect in MSSQL:
http://www.screenr.com/tPG

We rely on table elimination to get better performance in 3NF-views and
functions used in our Anchor Modeling (6NF-table) technique. However,
the need for table elimination has previously come from denormalized
modeling, where it is used to speed up queries against star schemas (e 
g
eliminating dimension tables). It should therefore not only be
appreciated by our crowd, if implemented in full.

We were very happy to see the 'outer join table elimination' 
implemented
and are wondering if someone could put in the effort to add the 
'foreign
key table elimination'? Since everything we do with Anchor Modeling is
Open Source, it would be nice to have an Open Source database that 
fully
supports the technique. Support in other databases is described here, 
if
anyone is interested: http://www.anchormodeling.com/?page_id=22

Thanks,
Lars