Sqlite database doesn't use existing index
0
I created indexes on 3 columns, but searching is still very slow. It appears index is not used at all.
sqlite> .schema
CREATE TABLE users(username, address, date_signup);
CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
0|0|0|SCAN TABLE users
sqlite>
How do I fix this?
Edit: sqlite version that I'm using is SQLite version 3.11.0 2016-02-15 17:29:24
database sqlite indexing sqlite3
asked Nov 11 '18 at 17:20
user2672932user2672932
6917
add a comment |
0
I created indexes on 3 columns, but searching is still very slow. It appears index is not used at all.
sqlite> .schema
CREATE TABLE users(username, address, date_signup);
CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
0|0|0|SCAN TABLE users
sqlite>
How do I fix this?
Edit: sqlite version that I'm using is SQLite version 3.11.0 2016-02-15 17:29:24
database sqlite indexing sqlite3
asked Nov 11 '18 at 17:20
user2672932user2672932
6917
Since both Shawn and I cannot reproduce your results, you need to give more info, in particular, which version of sqlite3 you are using.
– varro
Nov 11 '18 at 19:13
Updated the question to indicate I'm usingSQLite version 3.11.0 2016-02-15 17:29:24
– user2672932
Nov 12 '18 at 11:03
add a comment |
0
0
0
I created indexes on 3 columns, but searching is still very slow. It appears index is not used at all.
sqlite> .schema
CREATE TABLE users(username, address, date_signup);
CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
0|0|0|SCAN TABLE users
sqlite>
How do I fix this?
Edit: sqlite version that I'm using is SQLite version 3.11.0 2016-02-15 17:29:24
database sqlite indexing sqlite3
asked Nov 11 '18 at 17:20
user2672932user2672932
6917
I created indexes on 3 columns, but searching is still very slow. It appears index is not used at all.
sqlite> .schema
CREATE TABLE users(username, address, date_signup);
CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
0|0|0|SCAN TABLE users
sqlite>
How do I fix this?
Edit: sqlite version that I'm using is SQLite version 3.11.0 2016-02-15 17:29:24
database sqlite indexing sqlite3
database sqlite indexing sqlite3
asked Nov 11 '18 at 17:20
user2672932user2672932
6917
asked Nov 11 '18 at 17:20
user2672932user2672932
6917
edited Nov 12 '18 at 11:04
user2672932
asked Nov 11 '18 at 17:20
user2672932user2672932
6917
asked Nov 11 '18 at 17:20
user2672932user2672932
6917
asked Nov 11 '18 at 17:20
user2672932user2672932
6917
6917
Since both Shawn and I cannot reproduce your results, you need to give more info, in particular, which version of sqlite3 you are using.
– varro
Nov 11 '18 at 19:13
Updated the question to indicate I'm usingSQLite version 3.11.0 2016-02-15 17:29:24
– user2672932
Nov 12 '18 at 11:03
add a comment |
Since both Shawn and I cannot reproduce your results, you need to give more info, in particular, which version of sqlite3 you are using.
– varro
Nov 11 '18 at 19:13
Updated the question to indicate I'm usingSQLite version 3.11.0 2016-02-15 17:29:24
– user2672932
Nov 12 '18 at 11:03
Since both Shawn and I cannot reproduce your results, you need to give more info, in particular, which version of sqlite3 you are using.
– varro
Nov 11 '18 at 19:13
Since both Shawn and I cannot reproduce your results, you need to give more info, in particular, which version of sqlite3 you are using.
– varro
Nov 11 '18 at 19:13
Updated the question to indicate I'm using
SQLite version 3.11.0 2016-02-15 17:29:24– user2672932
Nov 12 '18 at 11:03
Updated the question to indicate I'm using
SQLite version 3.11.0 2016-02-15 17:29:24– user2672932
Nov 12 '18 at 11:03
add a comment |
2 Answers
2
active
oldest
votes
0
This statement:
select * from users where username = 'johndoe'
would run faster for sure if an index existed for the column username but there isn't one.
The index you created:
CREATE INDEX usersindex on users (username, address, date_signup)
is not an index for username but for all 3 columns username, address, date_signup.
Of course I don't know if this was your intention.
If you run SELECT statements including WHERE or JOIN clauses referring to these 3 columns together you should see an improvement.
answered Nov 11 '18 at 17:59
forpasforpas
9,5071421
Great, I feel stupid now.
– user2672932
Nov 11 '18 at 18:10
1
Er, no. Sinceusernameby itself is a prefix of the columns used in that index, it can use that index for that query. See sqlite.org/queryplanner.html#_multi_column_indices
– Shawn
Nov 11 '18 at 18:51
I hope what you mean is: that in situations where a triple index on(username, address, date_signup)is really needed, there is no need for another index on(username).<br/> I hope what you don't mean is: that in situations where a triple index on(username, address, date_signup)is not really needed, then an index on(username)only would not do better as the triple one.
– forpas
Nov 11 '18 at 19:16
1
I mean that OP's index should be getting used for queries that only look atusername, or onlyusernameandaddress, orusernameandaddressanddate_signup(But notaddressanddate_signupwithoutusername). That's how multi-column indexes work. And, indeed, that index is being used when other people try OP's example, including testing with a very old sqlite version. So OP has something else going on that they're not mentioning. The last sentence of your answer implies that the index shouldn't be used for the sample query, which is incorrect.
– Shawn
Nov 11 '18 at 19:39
My last sentence says: "if I need to search or join with theusernamecolumn I will not create a triple index even if it would as efficiently as the triple one (I don't believe there is any chance of it)". Just aside: I did not downovote your answer.
– forpas
Nov 11 '18 at 19:48
add a comment |
0
I'm unable to replicate.
$ sqlite3
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users(username, address, date_signup);
sqlite> CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
QUERY PLAN
`--SEARCH TABLE users USING COVERING INDEX usersindex (username=?)
sqlite>
As you can see, it's using the index as expected. From your EXPLAIN QUERY PLAN format, you're using an older version... just how old? It's possible changes and fixes to the query planner have been made since that one that might impact the results. You can go through the change log to see.
(The oldest version of the sqlite3 shell I have installed is 3.22, and that also uses the index.)
answered Nov 11 '18 at 19:00
ShawnShawn
3,5731613
1
I tried it on sqlite3 3.8.8.3 and got the same result (the index is used).
– varro
Nov 11 '18 at 19:11
@varro Thanks for testing an old version. Given that, I think OP has to be leaving something important out of their question. Not sure what, though.
– Shawn
Nov 11 '18 at 19:44
It's weird but I'm unable to replicate given your example on a newly created (empty) database as well. Query planner shows the index will be used. Although the real database where it doesn't work has absolutely the same schema.
– user2672932
Nov 12 '18 at 11:11
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53251251%2fsqlite-database-doesnt-use-existing-index%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
This statement:
select * from users where username = 'johndoe'
would run faster for sure if an index existed for the column username but there isn't one.
The index you created:
CREATE INDEX usersindex on users (username, address, date_signup)
is not an index for username but for all 3 columns username, address, date_signup.
Of course I don't know if this was your intention.
If you run SELECT statements including WHERE or JOIN clauses referring to these 3 columns together you should see an improvement.
answered Nov 11 '18 at 17:59
forpasforpas
9,5071421
Great, I feel stupid now.
– user2672932
Nov 11 '18 at 18:10
1
Er, no. Sinceusernameby itself is a prefix of the columns used in that index, it can use that index for that query. See sqlite.org/queryplanner.html#_multi_column_indices
– Shawn
Nov 11 '18 at 18:51
I hope what you mean is: that in situations where a triple index on(username, address, date_signup)is really needed, there is no need for another index on(username).<br/> I hope what you don't mean is: that in situations where a triple index on(username, address, date_signup)is not really needed, then an index on(username)only would not do better as the triple one.
– forpas
Nov 11 '18 at 19:16
1
I mean that OP's index should be getting used for queries that only look atusername, or onlyusernameandaddress, orusernameandaddressanddate_signup(But notaddressanddate_signupwithoutusername). That's how multi-column indexes work. And, indeed, that index is being used when other people try OP's example, including testing with a very old sqlite version. So OP has something else going on that they're not mentioning. The last sentence of your answer implies that the index shouldn't be used for the sample query, which is incorrect.
– Shawn
Nov 11 '18 at 19:39
My last sentence says: "if I need to search or join with theusernamecolumn I will not create a triple index even if it would as efficiently as the triple one (I don't believe there is any chance of it)". Just aside: I did not downovote your answer.
– forpas
Nov 11 '18 at 19:48
add a comment |
0
This statement:
select * from users where username = 'johndoe'
would run faster for sure if an index existed for the column username but there isn't one.
The index you created:
CREATE INDEX usersindex on users (username, address, date_signup)
is not an index for username but for all 3 columns username, address, date_signup.
Of course I don't know if this was your intention.
If you run SELECT statements including WHERE or JOIN clauses referring to these 3 columns together you should see an improvement.
answered Nov 11 '18 at 17:59
forpasforpas
9,5071421
Great, I feel stupid now.
– user2672932
Nov 11 '18 at 18:10
1
Er, no. Sinceusernameby itself is a prefix of the columns used in that index, it can use that index for that query. See sqlite.org/queryplanner.html#_multi_column_indices
– Shawn
Nov 11 '18 at 18:51
I hope what you mean is: that in situations where a triple index on(username, address, date_signup)is really needed, there is no need for another index on(username).<br/> I hope what you don't mean is: that in situations where a triple index on(username, address, date_signup)is not really needed, then an index on(username)only would not do better as the triple one.
– forpas
Nov 11 '18 at 19:16
1
I mean that OP's index should be getting used for queries that only look atusername, or onlyusernameandaddress, orusernameandaddressanddate_signup(But notaddressanddate_signupwithoutusername). That's how multi-column indexes work. And, indeed, that index is being used when other people try OP's example, including testing with a very old sqlite version. So OP has something else going on that they're not mentioning. The last sentence of your answer implies that the index shouldn't be used for the sample query, which is incorrect.
– Shawn
Nov 11 '18 at 19:39
My last sentence says: "if I need to search or join with theusernamecolumn I will not create a triple index even if it would as efficiently as the triple one (I don't believe there is any chance of it)". Just aside: I did not downovote your answer.
– forpas
Nov 11 '18 at 19:48
add a comment |
0
0
0
This statement:
select * from users where username = 'johndoe'
would run faster for sure if an index existed for the column username but there isn't one.
The index you created:
CREATE INDEX usersindex on users (username, address, date_signup)
is not an index for username but for all 3 columns username, address, date_signup.
Of course I don't know if this was your intention.
If you run SELECT statements including WHERE or JOIN clauses referring to these 3 columns together you should see an improvement.
answered Nov 11 '18 at 17:59
forpasforpas
9,5071421
This statement:
select * from users where username = 'johndoe'
would run faster for sure if an index existed for the column username but there isn't one.
The index you created:
CREATE INDEX usersindex on users (username, address, date_signup)
is not an index for username but for all 3 columns username, address, date_signup.
Of course I don't know if this was your intention.
If you run SELECT statements including WHERE or JOIN clauses referring to these 3 columns together you should see an improvement.
answered Nov 11 '18 at 17:59
forpasforpas
9,5071421
answered Nov 11 '18 at 17:59
forpasforpas
9,5071421
answered Nov 11 '18 at 17:59
forpasforpas
9,5071421
answered Nov 11 '18 at 17:59
forpasforpas
9,5071421
9,5071421
Great, I feel stupid now.
– user2672932
Nov 11 '18 at 18:10
1
Er, no. Sinceusernameby itself is a prefix of the columns used in that index, it can use that index for that query. See sqlite.org/queryplanner.html#_multi_column_indices
– Shawn
Nov 11 '18 at 18:51
I hope what you mean is: that in situations where a triple index on(username, address, date_signup)is really needed, there is no need for another index on(username).<br/> I hope what you don't mean is: that in situations where a triple index on(username, address, date_signup)is not really needed, then an index on(username)only would not do better as the triple one.
– forpas
Nov 11 '18 at 19:16
1
I mean that OP's index should be getting used for queries that only look atusername, or onlyusernameandaddress, orusernameandaddressanddate_signup(But notaddressanddate_signupwithoutusername). That's how multi-column indexes work. And, indeed, that index is being used when other people try OP's example, including testing with a very old sqlite version. So OP has something else going on that they're not mentioning. The last sentence of your answer implies that the index shouldn't be used for the sample query, which is incorrect.
– Shawn
Nov 11 '18 at 19:39
My last sentence says: "if I need to search or join with theusernamecolumn I will not create a triple index even if it would as efficiently as the triple one (I don't believe there is any chance of it)". Just aside: I did not downovote your answer.
– forpas
Nov 11 '18 at 19:48
add a comment |
Great, I feel stupid now.
– user2672932
Nov 11 '18 at 18:10
1
Er, no. Sinceusernameby itself is a prefix of the columns used in that index, it can use that index for that query. See sqlite.org/queryplanner.html#_multi_column_indices
– Shawn
Nov 11 '18 at 18:51
I hope what you mean is: that in situations where a triple index on(username, address, date_signup)is really needed, there is no need for another index on(username).<br/> I hope what you don't mean is: that in situations where a triple index on(username, address, date_signup)is not really needed, then an index on(username)only would not do better as the triple one.
– forpas
Nov 11 '18 at 19:16
1
I mean that OP's index should be getting used for queries that only look atusername, or onlyusernameandaddress, orusernameandaddressanddate_signup(But notaddressanddate_signupwithoutusername). That's how multi-column indexes work. And, indeed, that index is being used when other people try OP's example, including testing with a very old sqlite version. So OP has something else going on that they're not mentioning. The last sentence of your answer implies that the index shouldn't be used for the sample query, which is incorrect.
– Shawn
Nov 11 '18 at 19:39
My last sentence says: "if I need to search or join with theusernamecolumn I will not create a triple index even if it would as efficiently as the triple one (I don't believe there is any chance of it)". Just aside: I did not downovote your answer.
– forpas
Nov 11 '18 at 19:48
Great, I feel stupid now.
– user2672932
Nov 11 '18 at 18:10
Great, I feel stupid now.
– user2672932
Nov 11 '18 at 18:10
1
1
Er, no. Since
username by itself is a prefix of the columns used in that index, it can use that index for that query. See sqlite.org/queryplanner.html#_multi_column_indices– Shawn
Nov 11 '18 at 18:51
Er, no. Since
username by itself is a prefix of the columns used in that index, it can use that index for that query. See sqlite.org/queryplanner.html#_multi_column_indices– Shawn
Nov 11 '18 at 18:51
I hope what you mean is: that in situations where a triple index on
(username, address, date_signup) is really needed, there is no need for another index on (username).<br/> I hope what you don't mean is: that in situations where a triple index on (username, address, date_signup) is not really needed, then an index on (username) only would not do better as the triple one.– forpas
Nov 11 '18 at 19:16
I hope what you mean is: that in situations where a triple index on
(username, address, date_signup) is really needed, there is no need for another index on (username).<br/> I hope what you don't mean is: that in situations where a triple index on (username, address, date_signup) is not really needed, then an index on (username) only would not do better as the triple one.– forpas
Nov 11 '18 at 19:16
1
1
I mean that OP's index should be getting used for queries that only look at
username, or only username and address, or username and address and date_signup (But not address and date_signup without username). That's how multi-column indexes work. And, indeed, that index is being used when other people try OP's example, including testing with a very old sqlite version. So OP has something else going on that they're not mentioning. The last sentence of your answer implies that the index shouldn't be used for the sample query, which is incorrect.– Shawn
Nov 11 '18 at 19:39
I mean that OP's index should be getting used for queries that only look at
username, or only username and address, or username and address and date_signup (But not address and date_signup without username). That's how multi-column indexes work. And, indeed, that index is being used when other people try OP's example, including testing with a very old sqlite version. So OP has something else going on that they're not mentioning. The last sentence of your answer implies that the index shouldn't be used for the sample query, which is incorrect.– Shawn
Nov 11 '18 at 19:39
My last sentence says: "if I need to search or join with the
username column I will not create a triple index even if it would as efficiently as the triple one (I don't believe there is any chance of it)". Just aside: I did not downovote your answer.– forpas
Nov 11 '18 at 19:48
My last sentence says: "if I need to search or join with the
username column I will not create a triple index even if it would as efficiently as the triple one (I don't believe there is any chance of it)". Just aside: I did not downovote your answer.– forpas
Nov 11 '18 at 19:48
add a comment |
0
I'm unable to replicate.
$ sqlite3
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users(username, address, date_signup);
sqlite> CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
QUERY PLAN
`--SEARCH TABLE users USING COVERING INDEX usersindex (username=?)
sqlite>
As you can see, it's using the index as expected. From your EXPLAIN QUERY PLAN format, you're using an older version... just how old? It's possible changes and fixes to the query planner have been made since that one that might impact the results. You can go through the change log to see.
(The oldest version of the sqlite3 shell I have installed is 3.22, and that also uses the index.)
answered Nov 11 '18 at 19:00
ShawnShawn
3,5731613
1
I tried it on sqlite3 3.8.8.3 and got the same result (the index is used).
– varro
Nov 11 '18 at 19:11
@varro Thanks for testing an old version. Given that, I think OP has to be leaving something important out of their question. Not sure what, though.
– Shawn
Nov 11 '18 at 19:44
It's weird but I'm unable to replicate given your example on a newly created (empty) database as well. Query planner shows the index will be used. Although the real database where it doesn't work has absolutely the same schema.
– user2672932
Nov 12 '18 at 11:11
add a comment |
0
I'm unable to replicate.
$ sqlite3
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users(username, address, date_signup);
sqlite> CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
QUERY PLAN
`--SEARCH TABLE users USING COVERING INDEX usersindex (username=?)
sqlite>
As you can see, it's using the index as expected. From your EXPLAIN QUERY PLAN format, you're using an older version... just how old? It's possible changes and fixes to the query planner have been made since that one that might impact the results. You can go through the change log to see.
(The oldest version of the sqlite3 shell I have installed is 3.22, and that also uses the index.)
answered Nov 11 '18 at 19:00
ShawnShawn
3,5731613
1
I tried it on sqlite3 3.8.8.3 and got the same result (the index is used).
– varro
Nov 11 '18 at 19:11
@varro Thanks for testing an old version. Given that, I think OP has to be leaving something important out of their question. Not sure what, though.
– Shawn
Nov 11 '18 at 19:44
It's weird but I'm unable to replicate given your example on a newly created (empty) database as well. Query planner shows the index will be used. Although the real database where it doesn't work has absolutely the same schema.
– user2672932
Nov 12 '18 at 11:11
add a comment |
0
0
0
I'm unable to replicate.
$ sqlite3
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users(username, address, date_signup);
sqlite> CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
QUERY PLAN
`--SEARCH TABLE users USING COVERING INDEX usersindex (username=?)
sqlite>
As you can see, it's using the index as expected. From your EXPLAIN QUERY PLAN format, you're using an older version... just how old? It's possible changes and fixes to the query planner have been made since that one that might impact the results. You can go through the change log to see.
(The oldest version of the sqlite3 shell I have installed is 3.22, and that also uses the index.)
answered Nov 11 '18 at 19:00
ShawnShawn
3,5731613
I'm unable to replicate.
$ sqlite3
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users(username, address, date_signup);
sqlite> CREATE INDEX usersindex on users (username, address, date_signup);
sqlite> explain query plan select * from users where username = 'johndoe';
QUERY PLAN
`--SEARCH TABLE users USING COVERING INDEX usersindex (username=?)
sqlite>
As you can see, it's using the index as expected. From your EXPLAIN QUERY PLAN format, you're using an older version... just how old? It's possible changes and fixes to the query planner have been made since that one that might impact the results. You can go through the change log to see.
(The oldest version of the sqlite3 shell I have installed is 3.22, and that also uses the index.)
answered Nov 11 '18 at 19:00
ShawnShawn
3,5731613
answered Nov 11 '18 at 19:00
ShawnShawn
3,5731613
answered Nov 11 '18 at 19:00
ShawnShawn
3,5731613
answered Nov 11 '18 at 19:00
ShawnShawn
3,5731613
3,5731613
1
I tried it on sqlite3 3.8.8.3 and got the same result (the index is used).
– varro
Nov 11 '18 at 19:11
@varro Thanks for testing an old version. Given that, I think OP has to be leaving something important out of their question. Not sure what, though.
– Shawn
Nov 11 '18 at 19:44
It's weird but I'm unable to replicate given your example on a newly created (empty) database as well. Query planner shows the index will be used. Although the real database where it doesn't work has absolutely the same schema.
– user2672932
Nov 12 '18 at 11:11
add a comment |
1
I tried it on sqlite3 3.8.8.3 and got the same result (the index is used).
– varro
Nov 11 '18 at 19:11
@varro Thanks for testing an old version. Given that, I think OP has to be leaving something important out of their question. Not sure what, though.
– Shawn
Nov 11 '18 at 19:44
It's weird but I'm unable to replicate given your example on a newly created (empty) database as well. Query planner shows the index will be used. Although the real database where it doesn't work has absolutely the same schema.
– user2672932
Nov 12 '18 at 11:11
1
1
I tried it on sqlite3 3.8.8.3 and got the same result (the index is used).
– varro
Nov 11 '18 at 19:11
I tried it on sqlite3 3.8.8.3 and got the same result (the index is used).
– varro
Nov 11 '18 at 19:11
@varro Thanks for testing an old version. Given that, I think OP has to be leaving something important out of their question. Not sure what, though.
– Shawn
Nov 11 '18 at 19:44
@varro Thanks for testing an old version. Given that, I think OP has to be leaving something important out of their question. Not sure what, though.
– Shawn
Nov 11 '18 at 19:44
It's weird but I'm unable to replicate given your example on a newly created (empty) database as well. Query planner shows the index will be used. Although the real database where it doesn't work has absolutely the same schema.
– user2672932
Nov 12 '18 at 11:11
It's weird but I'm unable to replicate given your example on a newly created (empty) database as well. Query planner shows the index will be used. Although the real database where it doesn't work has absolutely the same schema.
– user2672932
Nov 12 '18 at 11:11
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53251251%2fsqlite-database-doesnt-use-existing-index%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Since both Shawn and I cannot reproduce your results, you need to give more info, in particular, which version of sqlite3 you are using.
– varro
Nov 11 '18 at 19:13
Updated the question to indicate I'm using
SQLite version 3.11.0 2016-02-15 17:29:24– user2672932
Nov 12 '18 at 11:03