Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account
List archives

Questions about table optimization

compile warning in tclsql 3.7.5
(33 lines)
Re: UPDATE/INSERTing 1-2k rows slower than expected
(111 lines)
Feb 12, 2011
Gabriele Favrin
Gabriele Favrin
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.





Reply
Tags: sqlitedev teamsql
Messages in this thread
Questions about table optimization
reply Re: Questions about table optimization
(36 lines) Feb 12, 2011 10:23
reply Re: Questions about table optimization
(62 lines) Feb 12, 2011 13:39
Virtual table optimization using xBestIndex/xFilter
December 19, 2010 06:21:39 AM
Hi, I'm using the SQLite virtual table mechanism to create an SQL interface for a specialized relational database that doesn't speak SQL natively. Thanks to Jay Kreibich's great "Using SQLite", the implementation has been pretty straightforward so…
Commented: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table functi
September 7, 2010 09:11:59 AM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId= 12906807#action_12906807 ] Rick Hillegas commented on DERBY-4789:
Commented: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table functi
September 7, 2010 09:21:37 AM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId= 12906813#action_12906813 ] Rick Hillegas commented on DERBY-4789:
Closed: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table function.
January 21, 2011 01:22:12 PM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas closed DERBY-4789.
Commented: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table functi
September 13, 2010 07:45:17 AM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId= 12908765#action_12908765 ] Rick Hillegas commented on DERBY-4789:
Updated: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table function
September 12, 2010 05:18:13 PM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:all-tabpanel ] Lily Wei updated DERBY-4789:
Updated: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table function
September 12, 2010 05:12:16 PM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:all-tabpanel ] Lily Wei updated DERBY-4789:
Updated: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table function
September 7, 2010 10:28:36 AM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:all-tabpanel ] Lily Wei updated DERBY-4789:
Updated: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table function
September 8, 2010 01:33:56 PM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:all-tabpanel ] Lily Wei updated DERBY-4789:
Commented: (DERBY-4789) Always apply the bulk-insert optimization when inserting from a table functi
September 7, 2010 11:53:00 AM
[ https://issues.apache.org/jira/browse/DERBY-4789?page=com.atlassian.ji ra.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId= 12906870#action_12906870 ] Rick Hillegas commented on DERBY-4789:
Research About query optimization and Index optimization of Derby
December 8, 2010 07:58:48 AM
Hi all, We are going to do a research project on the query optimization and Index optimization of Apache Derby. Our group consist of four of final year students of University of Moratuwa. We did a research on the idea and expect the help from…
Created: (DERBY-4883) Create a table function which turns an html table into a dbms table
November 2, 2010 04:02:50 PM
Create a table function which turns an html table into a dbms table
Updated: (DERBY-4639) Repeatedly issuing a CREATE TABLE against a table that already exists causes D
June 29, 2010 04:14:50 PM
[ https://issues.apache.org/jira/browse/DERBY-4639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mike Matrigali updated DERBY-4639:
Created: (DERBY-4681) Dropping a column in the table drops the views that use this table
May 28, 2010 06:15:40 AM
Dropping a column in the table drops the views that use this table
Closed: (DERBY-2251) When I Create a table with Chinese table name , ij report IO exception.
October 5, 2010 04:51:05 AM
[ https://issues.apache.org/jira/browse/DERBY-2251?page=com.atlassian.ji ra.plugin.system.issuetabpanels:all-tabpanel ] Tiago R. Espinha closed DERBY-2251.
Assigned: (DERBY-4639) Repeatedly issuing a CREATE TABLE against a table that already exists causes
June 7, 2010 04:29:49 PM
[ https://issues.apache.org/jira/browse/DERBY-4639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lily Wei reassigned DERBY-4639:
Updated: (DERBY-4639) Repeatedly issuing a CREATE TABLE against a table that already exists causes D
June 7, 2010 04:31:43 PM
[ https://issues.apache.org/jira/browse/DERBY-4639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lily Wei updated DERBY-4639:
Created: (HIVE-1480) CREATE TABLE IF NOT EXISTS get incorrect table name
July 22, 2010 02:18:49 PM
CREATE TABLE IF NOT EXISTS get incorrect table name
Created: (HIVE-1763) drop table (or view) should issue warning if table doesn't exist
November 2, 2010 03:36:55 PM
drop table (or view) should issue warning if table doesn't exist
Resolved: (DERBY-4479) after rename table a to b then create table a statement execute cause null po
July 2, 2010 04:20:49 PM
[ https://issues.apache.org/jira/browse/DERBY-4479?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mike Matrigali resolved DERBY-4479.
Few questions on planning mysql table
January 11, 2011
say I have a large table, 200 000 rows and I need to change/add column of enum type, would it work properly or would I experience problems? Should I…
SQL optimization on multi-table queries
January 10, 2011
I'm working with a large database of unemployment data built from the files available from the department of labor statistics here:…
Random row query optimization in innodb table
March 4, 2010
$offset = SELECT FLOOR(RAND() * COUNT(*)) FROM t_table SELECT * FROM t_table WHERE LIMIT $offset,1 This works great in myisam but i would like to…
Mod_rewrite and Apache questions
January 10, 2011
We have an interesting situation in relation to some help desk software that we are trying to setup. This is a web based software application that…
Im making a PHP game with a map, and i have some questions
January 10, 2011
Well im Making a PHP webgame with a map where players can walk on. but im very about overall performance of the game. it is a bit like this: The…
PHP : parsing questions then getting the keywords
February 10, 2011
I need to parse a question and then get the keywords and store them inside the database. Example: What is a void function? I can already take out…
Gzip - questions about performance
December 20, 2010
Firstly, I'm using Django. Django provides gzip middleware which works just fine. Nginx also provides a gzip module. Would it make more sense to…
Questions about salting passwords
January 12, 2011
Okay. Say for example that i set the salt for a password to "hello." Can't someone just look at the source code and discover the salt? If so, how…
Two questions about mongodb console
February 12, 2011
In the mongo shell: > db.thing_collection.find() { "_id" : ObjectId("4d5541bece6bff4a6d000000"), "views" : 0 } How to delete above record? {…
Questions on sqlite transactions
December 27, 2010
1) Is it ok to do inserts,deletes and updates in one transaction? 2) Is there a (recommended) limit to the number of writes per transaction?
Caching for database questions.
January 15, 2011
When we say caching like using memcahe or Redis, is this a 1:1 caching between the user and the cache or can we cache 1 item and use it for all…
Some questions related to SphinxSE and RT indexes
February 10, 2011
Hi. I consider using Sphinx search in one of my projects so I have a few questions related to it. When using SphinxSE and RT index, every UPDATE or…
General questions about index and mysql
January 28, 2011
Im trying to understand indexes better for when I use Mysql. One issue is Im still having a hard time to determine what type of index I should use…
Solr questions regarding handler resolution and escaping
January 9, 2011
I have a couple of questions regarding Solr usage: Certain requests can be sent to different paths (handlers?). For example, the MoreLikeThis…
Questions on accessing localhost of computer outside the network
January 24, 2011
I'm using wampserver. And I let my friends test the site that I made, by putting it online and giving them the ip address of my computer. The site…
How to write a .htaccess redirect like stackoverflow does for its questions
January 3, 2011
Hi all, I'm trying to write a .htaccess rule that would redirect someone asking for http://mysite.com/questions/123/my-question-name to…
Spring3, JAXB2, Java6, NamespacePrefixMapper questions
January 4, 2011
I built a simple Spring3, Hibernate3/(JPA2), RESTful service, hosted on Tomcat6, that uses JAXB2 to marshal the results. (It uses annotated pojos.)…
RegEx to Extract Multiple Choice Questions
February 8, 2011
Hey, I have 24 exams in a plain text file with 50 MC questions each plus a bunch of other stuff. Here is an example of what I'm trying to capture: 1…
Questions about making a website like reddit digg
February 11, 2011
Hey, I know this question gets asked quite a bit but I still have some lingering questions. I want to build a website similar to reddit/digg. I have…
Custom IP/UDP/RTP header in windows xp (and above) + general network questions
January 22, 2011
Hello, Lots of questions, I am sorry! I am doing a voice-chat (VoIP) application and I was thinking of doing a custom implementation of the…