Gabriele Favrin <[email protected]> wrote: > The board is moderated, so any new message should be approved from admin. > I use the columns pub to determine messages that can be shown and new to > determine new messages (which by default have pub set to 0). This is > because changing a message from new=1 to new=0 gets some points to the > user who sent it.
I'd have a separate table for new messages, and move them over to the main table as they become approved. > I've started to think: an index on pub and or new would speed up > counting and display, right? It wouldn't help much at best, and may actually hurt performance at worst. An index works best on column(s) with lots of distinct values, and for queries that need to quickly pick a small subset of a large set of rows (e.g., select all messages posted on a given date). > But doesn't it also slow down too much > insertion and update Measure it, but I doubt you'll see a noticeable difference. > Another question related to this table: is there any way to have a > select and collect both main messages and replies to them in an > heirchical way? Not really. Add a column ThreadId (an ID of the root post might do), assign it to all new posts in that thread. On display, retrieve all posts by thread ID, reconstruct the hierarchy in your application's code. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
