All Questions
8
questions
5
votes
0
answers
131
views
Why does DBD::SQLite have a different query plan for an SQL statement
I have an INSERT INTO... SELECT ... FROM SQL statement that runs acceptably fast when executed from SQLite's command line shell.
However, if I execute the same statement (copy/pasted) with Perl's DBI:...
- 40.2k
2
votes
0
answers
442
views
SQLite Query moving average + slopes
I have a table in SQLite dumped from a CSV logging file with 20-50 columns, up to 500k rows. I would like to be able to query various columns in a few ways: instantaneous value, moving average value, ...
- 218
0
votes
1
answer
27
views
Return types of glob() and like() and failure of using index although 'LIKE optimization' applies
I'm writing this finding due to auto-created SQL by EntityFramework (see related question):
When returning the result of glob() (or like()) it appears that the type of these functions is bit:
SELECT ...
- 3,746
1
vote
3
answers
91
views
Why a SQL query is slower than another if according to EXPLAIN QUERY PLAN it should be the opposite?
I have two tables
CREATE TABLE Categories (
Category INTEGER,
Id INTEGER,
FOREIGN KEY (Category) REFERENCES CategoriesInfo(Category)
)
CREATE TABLE 'CategoriesInfo' (
'Category' INTEGER ...
- 284k
4
votes
2
answers
12k
views
How to understand SQLite `EXPLAIN QUERY PLAN` result?
I have read that joins are better than subqueries.
But
EXPLAIN QUERY PLAN
SELECT Queue.Id, NULL
FROM Queue
INNER JOIN LastQueue
ON Queue.Id=LastQueue.Id
gives
Array
(
[0] => ...
- 284k
7
votes
1
answer
7k
views
sqlite3 select min, max together is much slower than select them separately
sqlite> explain query plan select max(utc_time) from RequestLog;
0|0|0|SEARCH TABLE RequestLog USING COVERING INDEX key (~1 rows) # very fast
sqlite> explain query plan select min(utc_time) ...
- 6,527
2
votes
1
answer
650
views
Query plan For Sqlite
Does Sqlite create a "query plan" for the complex queries? (I know mysql creates one and chooses the best of different available combination to execute the query). Is there any way i can get ...
- 109
29
votes
3
answers
27k
views
How can I analyse a Sqlite query execution?
I have a Sqlite database which I want to check the indexes are correct. MS SQL Analyser is great at breaking down the query execution and utilised indexes.
Is there a similar tool for Sqlite?
- 12.2k