SQLite - is order by selection possible?

[Iczer]

I have multiply words SQLite selection query like this:

$sSql_Query = "SELECT FullFilePath FROM DB WHERE FileName LIKE '%" & $Search_1 & "%' OR FileName LIKE '%" & $Search_2 & "%' OR FileName LIKE '%" & $Search_3 & " ORDER BY FileName"

It returned in "ORDER BY FileName", but is it possible to have return in order by how is search sentence is built - first $Search_1, then $Search_2 and then $Search_3 (and so on up to end of current query)?

[Zedna]

Use UNION

 

like this

$sSql_Query = "SELECT FullFilePath FROM DB WHERE FileName LIKE '%" & $Search_1 & "%' UNION SELECT FullFilePath FROM DB WHERE FileName LIKE '%" & $Search_2 & "%' UNION SELECT FullFilePath FROM DB WHERE FileName LIKE '%" & $Search_3

 

Edited April 26, 2015 by Zedna

[Iczer]

Hmm... i tried UNION, but results a still not in expected order...

[jchd]

When no order by clause is specified, SQL engines are free to return results in any order of their choice. Most of the times that means the simple order which you expect, namely the order resulting from inserts/deletes/replaces but one should never rely on this.

You can achieve the order you want by unioning resuts along with a select number, like this:

$sSql_Query = _
"select ffp from (" & _
"   SELECT 1 rank, filename, FullFilePath ffp FROM DB WHERE FileName LIKE '%" & $Search_1 & "%' " & _
"   union all " & _
"   SELECT 2 rank, filename, FullFilePath ffp FROM DB WHERE FileName LIKE '%" & $Search_2 & "%' " & _
"   union all " & _
"   SELECT 3 rank, filename, FullFilePath ffp FROM DB WHERE FileName LIKE '%" & $Search_3 & "%' " & _
") " & _
"ORDER BY rank, FileName"

 

Edited April 27, 2015 by jchd

[Iczer]

Thanks, it's working. For speeding up process - is creating index beforehand for FullFilePath, FileName or/and FullFilePath+FileName would be enough?

[jchd]

I don't believe any index would help and here is why.

The 3 sub-queries all need a full table scan since you search for substrings which can be in the middle of the searched column. SQLite query planner has an optimization for searches where the LIKE argument is a prefix of the string (e.g. "select this, that from mytable where column like 'abc%'"). Obviously no index can be used when the argument is '%abc%'.

Now comes the outer select: it needs to scan the temporary table created by the union of the 3 sub-queries, and this temp table doesn't have an index automagically created. Furthermore, the average use case is when the result set is relatively small but in such case and even if the query planner would be smart enough to build an index on rank, filename, that would take longer than just sorting the rows on the very same criterion: rank then filename.

Issuing a statement to increase SQLite cache size (default to 2000 pages) could possibly help, making full use of the cache created by the first sub-query in the subsequent other 2:

_SQLite_Exec($hDB, "PRAGMA cache_size = 20000;")

This statement is only persistent over the current connection.
 

[Iczer]

thanks