Re: [sqlite] Suggests for improving the SQLite website
On 09/11/2007, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > This is indeed a sad commentary on the state of the > world wide web that it is now necessary to specify > a font on every web page Oh well. What's about CSS? It should help in this case. -- Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
On 09/11/2007, A.J.Millan <[EMAIL PROTECTED]> wrote: > > > Regarding the basic "look" of the site, we were considering > > using a style similar to the once found at ActiveState > > > > http://www.activestate.com/ > > > > However the tendency in computers screen is wider than until now. Today the > standard is about 1440 pixels x 900, so a unique horizontal arrange y a > waste of space. The newspapers designers tend to be specialist in this, > and they use several columns to arrange the information. Obviously this is > not a newspaper, but IMHO tree columns would be good, at least in the main > page. Two for the rest. > > Good luck! I am owner of web site with ~ 15k uniq visitors/day and I can say this is not really true. For example this is statistics from google analytics: 1. 1024x76842.51% 2. 1280x1024 27.73% 3. 1280x80010.43% 4. 1152x8645.33% 5. 1440x9003.10% -- Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Disk caching impacts performance.
You can significant increase access speed by "ANALYZE" in some cases. -- Biomechanica Artificial Sabotage Humanoid
[sqlite] How to better save events (logs) in database?
Hello All, I have 3 entities: 1. town 2. person 3. alliance Links between entities: person owns the towns group of persons can unites into alliance CREATE TABLE town ( tid int, /* town id */ pid int, /* owner person id */ name char, ... ); CREATE TABLE person ( pid int, /* person id */ aid int, /* alliance of this person */ name char, ); CREATE TABLE allaince ( aid int, /* alliance id */ name char, ... ); Here is events: one person can capture town from another person person can change alliance to another or stay without it Task: Keep events in SQL DB in most appropriate format for handy view events for one person/alliance Currently i know two way of solving this task: 1. Simple create table like this: CREATE TABLE event ( stamp DATETIME, /* time of event */ type int, /* type of events, for ex. 1 - capture town, 2 - alliance change */ tid int,/* town id */ pid int,/* (old) person id */ aid int,/* (old) alliance id */ new_pid int,/* (new) person id */ new_aid int /* (new) allaince id */ ); So, if i want to view all events for alliance, query should be like this: SELECT * from event WHERE aid = $AID OR new_aid = $AID; and next i should resolve any tid/pid/aid into real names ... This solution is very simple and don't look very nice in retrospective of Normal Forms :/ 2. Very compact variant. For keep all events enough this simple table: CREATE TABLE event ( stamp DATETIME, type int, /* 1 - capture, 2 - alliance change */ entity_id int, /* should be tid if it's capture, or pid if it's alliance change */ old_id int /* should be pid if it's capture, or aid if it's alliance change */ ); for example capture of village v1 by person p1 from person p2 will looks like: (stamp1, 1, v1, p2) and so on... This variant requires many queries and program logic if i want to see events for alliance/player, BUT this construction looks very geek :-) Maybe here is some other solution? Not so stupid like 1 variant and not so geek like 2 variant? -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] explain query plan/timing
Hello All, I've tried to find documentation/help about how to read output from EXPLAIN QUERY PLAN but without success. Can you point me where i can get it. And another question about timing/profiling for SQLite. Patch from this location http://katastrophos.net/andre/blog/2007/01/04/sqlite-simple-timing-profiler-patch/ don't work with latest SQLite. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"
On Wed, 9 May 2007 21:00:46 +0400 Tomash Brechko <[EMAIL PROTECTED]> wrote: > On Wed, May 09, 2007 at 14:45:33 +, [EMAIL PROTECTED] wrote: > > You need an R-Tree index to do something like this. The > > public-domain version of SQLite only supports B-Tree indices. > > So, no, indices are not going to help you here. > > Alternatively to R-tree index, you may simply partition the space into > NxM cells, with, say, left and bottom border belonging to the cell > itself (while right and upper borders belonging to the right and upper > cells as their left and bottom borders respectively), and enumerate > these cells row-by-row like > > 10|11|12|13|14 > ---+--+--+--+--- >5| 6| 7| 8| 9 > ---+--+--+--+--- >0| 1| 2| 3| 4 > > > This way every point belongs to exactly one cell. Then you create > >CREATE TABLE map ( >x INTEGER, >y INTEGER, >name TEXT, >cell_no INTEGER >); >CREATE INDEX map_cell_no ON map (cell_no); > > When inserting a point, you compute its cell_no (something like > > cell_no(x, y) = y / cell_height * cells_in_row + x / cell_width; > > > ). When doing a region query, you compute a set of cell numbers that > intersect with a query window, accumulate them in a (memory) table > selected_cells, and then do > >SELECT map.* >FROM mem.selected_cells sc CROSS JOIN map ON sc.cell_no = map.cell_no; > > Better yet to compute two sets: those cells that reside completely > within the query window, and those that intersect window border. > Points from the latter cells should be filtered further. > > Reasonable cell dimensions based on typical query window size and > points distribution will give quite reasonable performance. Interesting idea. I'll try to test it. Dimension for my map is 800x800 and number of points 50,000+. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"
On Wed, 9 May 2007 11:08:26 -0400 "Samuel R. Neff" <[EMAIL PROTECTED]> wrote: > > I wonder if it would be beneficial to add an additional where clause which > can prefilter the data so you only need to perform the full calculation on a > subset of records. > > I haven't done the math, but let's supposed that point_x is 10 and that for > any result of your long calculation to be true, then x must be between 5 and > 15, then you can use the where clause > > WHERE > X BETWEEN 5 AND 15 > AND (point_x - x)^2 + (point_y -y)^2 < R^2; > Yes... it should help a little :) -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"
On Wed, 09 May 2007 14:45:33 + [EMAIL PROTECTED] wrote: > bash <[EMAIL PROTECTED]> wrote: > > > > Oh... so this is implementation limitation. > > Im currently thinking about this table: > > > > CREATE TABLE map ( > > x int, > > y int, > > name char > > ); > > CREATE INDEX map_x ON map(x); > > CREATE INDEX map_y ON map(y); > > > > And query for it will be something like this (circle): > > SELECT name > > FROM map > > WHERE (point_x - x)^2 + (point_y -y)^2 < R^2; > > > > How SQLite will works? Is there any benefit in indexes? > > > > You need an R-Tree index to do something like this. The > public-domain version of SQLite only supports B-Tree indices. > So, no, indices are not going to help you here. Thanks for advice. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"
On Wed, 9 May 2007 18:13:07 +0400 Tomash Brechko <[EMAIL PROTECTED]> wrote: > On Wed, May 09, 2007 at 17:45:52 +0400, bash wrote: > > > One index per table rule. At first glance it seems like SQLite could > > > use at least one index for "x=5 OR y=7" case too, but there is no > > > point in that, as the other part of the OR would require full table > > > scan anyway. > > > > Why full table scan? :/ > > SQLite can takes set (1) of rowid by ex(x) index for > > "X=5". Then takes another set (2) of rowid by ex(y) for "Y=7". > > Then SQLite need only to union this two set (1) and (2). > > Final SQLite should returns rows where rowid in (set1 union set2). > > You should read it the following way: "SQLite can't use two indexes > per table, and using only one index is pointless, hence no index is > used at all". > > So your question is actually "why SQLite uses at most one index per > table?". My guess is that the benefits are out-weighted by the > implementation complexity. Oh... so this is implementation limitation. Im currently thinking about this table: CREATE TABLE map ( x int, y int, name char ); CREATE INDEX map_x ON map(x); CREATE INDEX map_y ON map(y); And query for it will be something like this (circle): SELECT name FROM map WHERE (point_x - x)^2 + (point_y -y)^2 < R^2; How SQLite will works? Is there any benefit in indexes? -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"
On Wed, 9 May 2007 17:29:29 +0400 Tomash Brechko <[EMAIL PROTECTED]> wrote: > > On Wed, May 09, 2007 at 16:32:34 +0400, bash wrote: > > SELECT * FROM ex1 WHERE x>'abc' AND y>'abc'; > > In this form only one indexes will be used, why not both? > > One index per table rule. At first glance it seems like SQLite could > use at least one index for "x=5 OR y=7" case too, but there is no > point in that, as the other part of the OR would require full table > scan anyway. Why full table scan? :/ SQLite can takes set (1) of rowid by ex(x) index for "X=5". Then takes another set (2) of rowid by ex(y) for "Y=7". Then SQLite need only to union this two set (1) and (2). Final SQLite should returns rows where rowid in (set1 union set2). > And for the solution with the UNION, > > sqlite> explain query plan > ...> SELECT id, n1, n2 > ...> FROM tbl > ...> WHERE n1 = $I > ...> UNION > ...> SELECT id, n1, n2 > ...> FROM tbl > ...> WHERE n2 = $I > ...> ORDER BY id DESC; > 0|0|TABLE tbl WITH INDEX idx1 > 0|0|TABLE tbl WITH INDEX idx2 Yep... Im using now this construction. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: perfomance degradation for expr "foo = X or bar =X"
On Wed, 9 May 2007 14:24:27 +0400 Tomash Brechko <[EMAIL PROTECTED]> wrote: > On Wed, May 09, 2007 at 14:03:54 +0400, bash wrote: > > Im simplify environment: > > > > CREATE TABLE tbl( > > id integer NOT NULL PRIMARY KEY AUTOINCREMENT, > > n1 int, > > n2 int > > ); > > CREATE INDEX idx1 on tbl(n1); > > CREATE INDEX idx2 on tbl(n2); > > > > sqlite> select count(*) from tbl; > > 63026 > > > > 1 query: > > SELECT id, n1, n2 > > FROM tbl > > WHERE n1 = $I OR n2 = $I > > ORDER BY id DESC; > > > > 2 query: > > SELECT id, n1, n2 > > FROM tbl > > WHERE n1 = $I > > UNION > > SELECT id, n1, n2 > > FROM tbl > > WHERE n2 = $I > > ORDER BY id DESC; > > > > Timing: > > 1: 0.080 s. > > 2: 0.000 s. > > From http://www.sqlite.org/optoverview.html section 6.0: > > Each table in the FROM clause of a query can use at most one index... > > So the first query can't benefit from both idx1 and idx2. You may use > > EXPLAIN QUERY PLAN SELECT ... > > to see what indexes will be used. If i understand right from previous post by Peter there will be not used any indexes because of "OR". -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"
On Wed, 9 May 2007 12:23:14 +0200 Peter van Dijk <[EMAIL PROTECTED]> wrote: > > On 9-mei-2007, at 11:28, bash wrote: > > > SELECT type, stamp_id, old_player_id, new_player_id > > FROM town_log > > WHERE old_player_id = $ID OR new_player_id = $ID > > ORDER BY stamp_id DESC; > > > > This query works really slowly and i don't know why :/ > > For example, the same result by another QUERY work much faster! > > > > SELECT type, stamp_id, old_player_id, new_player_id > > FROM town_log > > WHERE old_player_id = $ID > > UNION > > SELECT type, stamp_id, old_player_id, new_player_id > > FROM town_log > > WHERE new_player_id = %d > > ORDER BY stamp_id DESC; > > Hello Humanoid, > > UNION performing much better than an equivalent query with OR is a > known 'limitation' in many database systems, including MySQL and > SQLite. Page 52 of http://www.sqlite.org/php2004/slides-all.html > explicitly recommends to use UNION here. > > Cheers, Peter. Thanks a lot! I don't know that information :) Moreover this papers is really interesting. But i don't understand about indexes this: http://www.sqlite.org/php2004/slides-all.html Page 54 of 63 For example: CREATE TABLE ex1( id INTEGER PRIMARY KEY, x , y ); CREATE INDEX idx1 ON ex1(x); CREATE INDEX idx2 ON ex1(y); SELECT * FROM ex1 WHERE x>'abc' AND y>'abc'; In this form only one indexes will be used, why not both? For example by idx1 we can get set1 of ROWIDs which is satisfy "x > 'abc'" term, by idx2 we can get set2 of ROWIDs which is satisfy "y>'abc'" term and then just union set1 with set2. So next we should just extracts rows with ROWIDS in this union -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: perfomance degradation for expr "foo = X or bar =X"
Im simplify environment: CREATE TABLE tbl( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, n1 int, n2 int ); CREATE INDEX idx1 on tbl(n1); CREATE INDEX idx2 on tbl(n2); sqlite> select count(*) from tbl; 63026 1 query: SELECT id, n1, n2 FROM tbl WHERE n1 = $I OR n2 = $I ORDER BY id DESC; 2 query: SELECT id, n1, n2 FROM tbl WHERE n1 = $I UNION SELECT id, n1, n2 FROM tbl WHERE n2 = $I ORDER BY id DESC; Timing: 1: 0.080 s. 2: 0.000 s. Still big degradation. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] perfomance degradation for expr "foo = X or bar =X"
Hello All, Im using SQLite-3.3.17. My table is: CREATE TABLE town_log ( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, town_id int, stamp_id int, old_player_id int, new_player_id int, type int ); CREATE INDEX tl_np_id on town_log(new_player_id); CREATE INDEX tl_op_id on town_log(old_player_id); CREATE INDEX tl_st_id on town_log(stamp_id); CREATE INDEX tl_tw_id on town_log(town_id); CREATE INDEX tl_type on town_log(type); And I'm trying to execute this query: SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE old_player_id = $ID OR new_player_id = $ID ORDER BY stamp_id DESC; This query works really slowly and i don't know why :/ For example, the same result by another QUERY work much faster! SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE old_player_id = $ID UNION SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE new_player_id = %d ORDER BY stamp_id DESC; Timing by http://katastrophos.net/andre/blog/2007/01/04/sqlite-simple-timing-profiler-patch/ shows this times: 1st query: 0.250 s. 2st query: 0.000 s. PS. ANALYZE do not help to solve this problem. I think problem(?) in query optimizer. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
