2015-12-12 21:45 GMT+01:00 Richard Hipp <drh at sqlite.org>: > On 12/12/15, Cecil Westerhof <cldwesterhof at gmail.com> wrote: > > I am playing with SQLite. I am thinking about writing an application for > > projects. At the moment I have the following: > > > > CREATE TABLE `projects` ( > > `projectID` TEXT PRIMARY KEY, > > `groupID` TEXT, > > `isPersonal` INTEGER NOT NULL CHECK(isPersonal in (0, 1)), > > `name` TEXT, > > `description` TEXT, > > `outcome` TEXT > > ); > > CREATE INDEX projects_groupID_idx > > ON projects(groupID); > > CREATE INDEX projects_isPersonal_idx > > ON projects(isPersonal); > > > > ?I like to differentiate between personal and non personal projects. Is > it > > a good idea to put a index on isPersonal? > > No, usually not. An exception would be if the boolean is almost > always true or almost always false. Then using a partial index on > (the infrequent value of) that boolean might make sense. >
?I do not think that will be the case. It is even possible that one time it is mostly true and another time mostly false. I will remove the index. > > By the way: I am thinking about using UUID for projectID and groupID, > but I > > heard somewhere that it was a bad idea to use UUID for an indexed field. > Is > > this true?? > > > > I think you might have misunderstood. UUID is almost always a good > field to index. > ?I was told because of the nature of random UUID (what I will be using) it is hard to create a good index. The article said that data that is really random cannot be indexed very efficient. But I do not have to worry about it then. :-) It has been a few years back, so it is also possible that the problem is solved nowadays. ? > One other point: The use of grave accents to quote column names is a > mysql-ism. SQLite also supports that for compatibility. But you > still shouldn't do it. The proper SQL-standard way is double-quote. > ?That is funny: I did not use them at first (or double). But I am using 'DB Browser for SQLite' and this shows those, so I thought that ? ?I should use them.? -- Cecil Westerhof
