On 12 Feb 2011, at 1:25pm, Gabriele Favrin wrote: > First of all a BIG thanks to the dev team of SQLite, it's an amazing > library, it helped me to enter in the world of SQL and I'm using it from > PHP on some small-medium sites. Thanks also to who takes time to explain > thing to people who aren't really expert on db (like me, I know a bit > but I'm so much to learn).
In case anyone's wondering it really is nice to be thanked occasionally. > ip VARCHAR( 16 ), > msg VARCHAR( 4096 ), Please note that these types don't exist in SQLite. They're both interpreted as 'TEXT' and the number of characters is not limited. Don't worry, this does not make use of TEXT columns slow, and the columns don't waste filespace for characters you don't use. For more details see http://www.sqlite.org/datatype3.html > pub INTEGER DEFAULT ( 0 ), > new INTEGER DEFAULT ( 1 ), Do you really need both fields ? Are all four combinations possible ? It seems to me that your real-life situation is that a new message has status new == awaiting moderation == not public and at some point the message is seen by a moderator and is either deleted (removed from the database) or becomes no longer new == moderator-approved == public So the two columns can only really need to be one column. Saving a column will speed things up generally. Another way to look at this might be to use many different values, since the column is already INTEGER not BOOL. So you might use something like 0 = new 1 = awaiting moderation (might be the same as 'new' in your system) 2 = moderated okay for normal post 3 = sticky 4 = important -1 = moderator did not approve (but saved in case of overruling, or as a record of the submitter's history) In that situation moderator-tasks can look for status 1, the public fora show posts of status >= 2, etc.. I just did this off the top of my head and you should make up your own numbers to reflect the way you want your fora to work. Merging these four columns into one (by including the sticky and important columns too) would save you database space and processing overhead every time to you make or delete a row. And just one index on (category,status) should be all you need to make all the searches fast. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
