Hello, think I got it, but it is disappointingly simple, see below. Ed.
Markus Gritsch wrote:
Even more strange:
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('tes*',))
takes less than 1ms but
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('test',))
takes several hundred ms.
The execute in Python includes prepare (or get from cache), bind and
the first step.
The answer must be that the wait time lies in the first step.
The engine is doing a full scan and it all depends how far in the
table it needs to go to find the first match.
So the bind values with * just come across a match sooner.
Wilhelm Braun wrote:
I just tried for fun:
start = time.time()
SQLString=('''SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB '%s'
''' % "hui*")
c.execute(SQLString)
and it is as fast as your first one - seems a pysqlite problem to me
I know they say this is not a secure way to do it -- well.
This seems the only solution after all.
But it floods the wonderful pysqlite statement cache, with new SQL
statements for each new bind value.
Preferably, only the operator is substituted in the SQL, for "GLOB"
or just "=", depending on the actual bind value.
That leaves just two different statements.
But I don't know if the result is the same as I don't know GLOB very
well.
If it is affected by the case_sensitive_like pragma my idea is too
simple.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
