On 6 Jan 2018, at 6:41am, Nick <haveagoodtime2...@gmail.com> wrote:
> I find it is indeed faster than t2(c).
If you want to know which is the best index, create all the indexes you think
might be good, run ANALYZE, then use
EXPLAIN QUERY PLAN SELECT (rest of SELECT statement here)
and see which index SQLite chooses to use. Then you can delete the index(es)
it didn’t choose.
Note that this gives accurate results only if your tables have convincing data
in. It’s not pointless, but nowhere near as accurate, if you're running it on
a test database with 10 entries.
> Or in another word, if a TEXT column has similar meaning with an INTEGER
> column in my applications,(such as use userID instead of userName, still the
> way that the data works in my head:) ) is it recommended to use INTEGER one
> in order to get a less index pages?
The correct way to arrange your data is to have userID everywhere you don’t
need to know the actual name. UserID can always remain the same but people
change their names. You wouldn’t want to have to go update your invoice table
every time someone got married, would you ?
When you need to print out your invoices showing the actual name, that’s what
JOIN is for.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
