the 2004 International PHP conference (slides 56-58), but I still feel like I'm stumbling around in the dark
when it comes to writing optimizedl SQL queries (specifically, SELECTS + JOINS).
I know about using EXPLAIN to see the opcodes generated for a query. I've seen people post
benchmarks by piping a single query into the sqlite3 command line tool. But for me, looking at opcodes
isn't very helpful and the queries I'm trying to optimize are rather complicated with multiple substitutions
and JOINS between 3 or 4 tables.
So I was thinking that a useful addition would be a function along the lines of sqlite3_last_insert_rowid()--
sqlite3_last_query_time(). Call it after sqlite3_exec(), sqlite3_get_table() or sqlite3_finalize() and you get
CPU ticks or elapsed milliseconds. Call it after sqlite3_step() and you get elapsed time for just that row.
Or maybe a debug mode where the full text of all queries and their elapsed time are automatically inserted
as rows into a (temporary?) table. With something like this, one can fiddle with their queries and easily
check to see if there is any benefit to rearranging them.
Is this just wishful thinking? Am I better off spamming the list with schemas and queries?
Thanks, Eli
