And here is a copy of my answer posted at stackoverflow (which assumes the context of the question):
A see a few problems here. 1. The first SQL statement (CREATE TABLE ...) is malformed due to an extra comma between the last column and the closing parenthesis. 2. The third SQL statement (EXPLAIN QUERY PLAN SELECT ...) uses double quotes around the specified email. SQLite will allow this for backward compatibility reasons, but it is not recommended. 3. The quoting of the string representing the third SQL statement is not quoted properly in any language I'm familiar with. 4. Finally, the table name social is not defined in the limited schema you gave, so really we have no way of knowing what real indexes are available. If we assume you meant "user" when you typed "social", as CL. said, the "autoindex" in use guarantees the email is unique so it doesn't need anything else. It might have used your explicit index as a covering index if you didn't need the name column as well, but since your explicit index does not include the name column (as required by the select statement), it deems the autoindex best. Additionally, the autoindex is almost certainly better for this case because the smaller index (only email vs email & password) means that there will likely be fewer reads of btree pages while trying to find the requested email address. In other words, an index on email address only will be a smaller index than an index on email and password.
