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.

sqlite-users mailing list

Reply via email to