Mega SQL

Mega SQL is the internal code name for the SQL behind the prefix search. It's rather large and has thus become known as the Mega SQL.

Although it does work, it has problems, most notably that it's pretty unwieldy.

There is some nesting of SQL which means that the inner query needs to complete before the outer query can start working. This in turn means that start-up time (the time for the first row to be returned) for the query is increased. If a query is ordered along a index it usually means that the database will follow the index, see if a row matches all conditions and then return that row, move on to the next and so on. The first row will be returned very quickly. If instead a query is nested, the inner parts will need to be run to completion (hitting LIMIT or returning all rows) before the outer parts can continue. That creates a "longer chain" which obviously is slower.

The innermost nest could probably be removed and just be replaced with conditions on the p2-join directly.

The parent_prefix part changes the structure of the Mega-SQL. If parent_prefix is specified, the JOIN between p1 and p2 is a LEFT OUTER JOIN while it is an INNER JOIN normally. It's not elegant to change the structure of the query and having implemented this, I threw up a little in my mouth.

I believe the parent_prefix part can be solved simply by moving away from explicit joins to an implicit join structure and placing the conditions in the WHERE part of the inner query.