Hi all, I'm new on this list.
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).
I'm using SQLite in web development since 2009.
Recently I moved my development from PHP 5.2.x (which contained old
SQLite version 3.3.7) to latest 5.3.x (with 3.7.3 and FTS3) and I've got
a very good increase of speed in the database related operations.
By reading this list I've started wondering if I can archieve even more
by improving indexing in my tables, so here I'm.
I've this table which is used for a board on a safe site for kids.
It's also going to become a forum with some more columns and another
table for topics:
CREATE TABLE bacheca (
id INTEGER PRIMARY KEY AUTOINCREMENT,
idnick INTEGER REFERENCES utenti ( id ) ON DELETE CASCADE,
ip VARCHAR( 16 ),
msg VARCHAR( 4096 ),
msg_date DATE DEFAULT ( CURRENT_TIMESTAMP ),
pub INTEGER DEFAULT ( 0 ),
sticky INTEGER DEFAULT ( 0 ),
important INTEGER DEFAULT ( 0 ),
new INTEGER DEFAULT ( 1 ),
category INTEGER DEFAULT ( 1 ),
replyto INTEGER REFERENCES bacheca ( id ) ON DELETE
CASCADE
);
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.
When I show messages to user I use the condition 'where pub=1 AND new=0'
(just to be safe). In the site control panel main page I collect new
activities to do which a 'select count(1) from bacheca where new=1' to
show if and how many new messages needs to ne approved.
Currently I have over 3600 messages in the board, showed with pagination
(15 per page). The thing is still fast but obviously slower than other
tables I have (for polls, user contributed calendar and so on).
I've started to think: an index on pub and or new would speed up
counting and display, right? But doesn't it also slow down too much
insertion and update (which from that I understand means that during
that time no one can't access any page which reads from database)? FYI,
insertion happens between a declared transaction since I've to update
other tables as well.
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?
Example: currently I don't have topics (will add them soon, as an
integer referencing another table for their names) but I've answers to a
specific message. How can I get with a single query ALL messages and
when one of them have ansers all of them? eg:
message id 1 has replies with id 2 3 4 6
A select should give me:
1
2
3
4
6
and than go on with message 5 which has no replies, and so on...
Or such a select would be complicated and is best to count so an index
for replyto is required, and it's already there) and do a 2nd select?
Thanks in advance and sorry for my BAD english.
--
Saluti da Gabriele Favrin
http://www.favrin.net
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
