[sqlite] Optimising a query with several criteria

Hey guys. I've optimised most of my queries to work effectively, but I have one which is sometimes causing me problems. It is: SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?') AND

Re: [sqlite] Optimising a query with several criteria

; Senior Scientist > NG Information Systems > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Ian Hardingham [i...@omroth.com] > Sent: Sunday, March 13,

[sqlite] X most recent entries

Hey guys. I have a table with an autoincrement primary ID, and as part of a select I would like to only take the 5000 "largest"/most recent ids. Is there a quick way of doing this without having to get the max first? Thanks, Ian ___ sqlite-users

Re: [sqlite] X most recent entries

the last 5000 for any SELECTs from multiturnTable. Thanks, Ian On 14/03/2011 17:54, Adam DeVita wrote: > select id from table order by id desc limit 5000 > > > Adam > > On Mon, Mar 14, 2011 at 1:52 PM, Ian Hardingham <i...@omroth.com > <mailto:i...@omroth.com>> w

[sqlite] Only select most recent results takes longer

Hey guys. Due to some help I had yesterday, I was advised to change this query: SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 OR

Re: [sqlite] 'integer'

I'm new around here - exactly what element of SQLite precludes it from being a database? On 17/04/2011 13:12, Stefan Keller wrote: > Michael and Jay are right about the subtleties on how SQlite > interprets what is a data type, a primary key and a database schema > and it's ACID implementation

[sqlite] Memory leak in SQlite

Hey guys. I'm sure that this is to do with the way I am using SQLite. I do not have time to radically change my methodology at this point, but I do need to fix a rather severe memory leak I'm having. (My apologies for the code) Any help is much appreciated. I query like this: int

[sqlite] Biggest number in an INTEGER field

Guys, an an SQLite3 INTEGER field what is the maximum number that fits in an INTEGER PRIMARY KEY field? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] TRANSACTIONs

Hey guys, thanks for all the help so far today. From within a BEGIN TRANSACTION and END TRANSACTION block, should I not update the same row in a table more than once? What are the exact limitations on what I can do during a Transaction? Thanks, Ian

[sqlite] Do I need to migrate to MySQL?

Guys, the server for this game - http://www.frozensynapse.com uses SQLite. We've had an unexpectedly successful launch which has resulted in the server being swamped with players, and I'm trying to optimise everywhere I can. I've always been under the impression that SQLite is pefectly

Re: [sqlite] Do I need to migrate to MySQL?

Thanks Eduardo, I will go into more detail. The core of the server is the match list. It is a table with currently about 200,000 rows in it. Two players will start a match, and a new entry is placed in the matchTable. A typical match will last 8 turns - as each player finishes a turn, the

Re: [sqlite] Do I need to migrate to MySQL?

:57, BareFeetWare wrote: > On 03/06/2011, at 9:47 PM, Ian Hardingham wrote: > >> What is basically happening is that we're getting a fairly large number >> of requests every second. There is one specific activity which takes >> about 2 seconds to resolve, which is finishi

Re: [sqlite] Do I need to migrate to MySQL?

Thank you Igor, I'll do some more thorough profiling. When I run the query: UPDATE multiturnTable SET complete=1 WHERE id=-5 This takes ~45ms (as reported by SQLite's profile) - is this in the right ballpark? I'm running a fairly fast modern intel chip here. Thanks, Ian

Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, once again thanks for the help. Should really every single INSERT/UPDATE section have a begin/end transaction around it? I have posted this code before, so apologies for doing it again - here is how my scripting language calls a query: int SQLiteObject::ExecuteSQL(const char* sql,

Re: [sqlite] Do I need to migrate to MySQL?

: > > On Jun 3, 2011 10:04 AM, "Ian Hardingham" <i...@omroth.com > <mailto:i...@omroth.com>> wrote: > > > > Thank you Igor, I'll do some more thorough profiling. > > > > When I run the query: > > > > UPDATE multiturnTable SET complete=1 WHERE

Re: [sqlite] Do I need to migrate to MySQL?

Thank you Igor, I'll do some more thorough profiling. When I run the query: UPDATE multiturnTable SET complete=1 WHERE id=-5 This takes ~45ms (as reported by SQLite's profile) - is this in the right ballpark? I'm running a fairly fast modern intel chip here. Thanks, Ian

Re: [sqlite] Do I need to migrate to MySQL?

Tom, thank you so much for the extensive advice. Ian On 04/06/2011 15:27, BareFeetWare wrote: > On 03/06/2011, at 11:40 PM, Ian Hardingham wrote: > >> Hey guys, thank you very much for the help so far. >> >> The list of calls which I make during the "end match

[sqlite] Using the same database from four separate applications

Hey guys. I believe it's fine to have four applications open the same database file and use it concurrently. I have a few questions. 1. Do I need to use any special PRAGMA or other option to use this functionality best? 2. Is it possible to (ab)use SQLite to perform as some kind of mutex?

Re: [sqlite] Using the same database from four separate applications

Hey Simon, thanks for this. I would really like to only block specifically one operation, rather than not allowing any database access in the exclusive block - is this possible? Thanks, Ian > I'm not certain I understand your question but SQLite performs a kind of > mutexing by default. If

[sqlite] Store result of calculation or not

Guys, my apologies for spamming the list today. A topic I've talked about before, but am just revisiting. I often need to get the "record" between two people - how many games they've won and lost against each other. For reference, the query is at the end of the email. Once again,

Re: [sqlite] Using the same database from four separate applications

12:07pm, Ian Hardingham wrote: > >> I believe it's fine to have four applications open the same database >> file and use it concurrently. > Assuming you are compiling without any special directives which turn this > ability off. For instance, read about everything mentioned i

Re: [sqlite] Using the same database from four separate applications

Thanks again Simon, I am actually asking for queries that may well not be in an EXCLUSIVE section, but I've realised that I can simulate the blocking in my own application by busy-waiting. Ian On 12/06/2011 15:16, Simon Slavin wrote: > On 12 Jun 2011, at 3:05pm, Ian Hardingham wr

Re: [sqlite] Store result of calculation or not

are different - I am looking for games where player1 is Ian and player2 is Igor, but I also want games where player1 is Igor and player2 is Ian. Cheers, Ian On 12/06/2011 15:28, Igor Tandetnik wrote: > Ian Hardingham<i...@omroth.com> wrote: >> I often need to get the "record

[sqlite] Implementing busy-waiting on SQLite

Hey guys. As was being discussed yesterday, I have four processes accessing the same database file. When they perform an sqlite action, I wish them to block if the DB is not available. SQLite does not block if it finds the db busy or locked, it returns an error code. I plan on using

[sqlite] Using the same database from four separate applications

Hey guys. I believe it's fine to have four applications open the same database file and use it concurrently. I have a few questions. 1. Do I need to use any special PRAGMA or other option to use this functionality best? 2. Is it possible to (ab)use SQLite to perform as some kind of mutex?

Re: [sqlite] Store result of calculation or not

are different - I am looking for games where player1 is Ian and player2 is Igor, but I also want games where player1 is Igor and player2 is Ian. Cheers, Ian On 12/06/2011 15:28, Igor Tandetnik wrote: > Ian Hardingham<i...@omroth.com> wrote: >> I often need to get the "record

[sqlite] To index or not to index?

Hey guys, once again sorry for spamming at the moment. This is a simple question. My user account table has a field "isOnline INT". This table has, say, 100,000 rows. Every ten seconds I need to compile a list of all users where isOnline is 1. However, people log in and out at a rate of

[sqlite] Multiple relations

Guys, I have another DB design question. I have 100 - 1 clients connected to my server. Each client has a status. Many clients are "watching" *one* other client, which means that any change in that client's status must immediately be sent to the watching clients. Estimates of numbers: -

[sqlite] SQLITE_CANTOPEN on an open database

Hey guys. We revisit my situation - I'm accessing the same database with 4 processes and they are using busy-waiting to access the db somewhat concurrently. I'm expecting SQLITE_LOCKED and SQLITE_BUSY, but I also get SQLITE_CANTOPEN - this is on an *already open* database and a query shortly

Re: [sqlite] SQLITE_CANTOPEN on an open database

Thanks Jay, that clears things up for me. On 20/06/2011 14:17, Jay A. Kreibich wrote: > On Mon, Jun 20, 2011 at 02:04:36PM +0100, Ian Hardingham scratched on the > wall: >> Hey guys. >> >> We revisit my situation - I'm accessing the same database with 4 >> pro

[sqlite] Simple schema design help

Hey guys. I have an existing table, the matchTable, where each entry holds a lot of information about a "match". I am adding a tournament system, and each match will either be in a tournament or not in a tournament. Should I add a "tournamentID" column to matchTable? Or should I create a

Re: [sqlite] Simple schema design help

Hey Tom, many thanks for the help. At times I will need to identify whether a match is a "tournament match" or not. It seems from what you're suggesting that I should do a select on the tournamentMembershipTable (with zero results being "no") rather than having a tournamentMatch boolean in

[sqlite] Defining a relationship as unique

Hey guys. I have this table: tournamentParticipantTable id INTEGER PRIMARY KEY user INTEGER tournamentId INTEGER I'm obviously going to put an index on both user, tournamentId and tournamentId, user - but as the relation is unique, I was wondering if I could in some way let SQLite know that?

Re: [sqlite] Defining a relationship as unique

Hey guys, thank you all for the help. I need to look into foreign keys. On 30/06/2011 13:31, Black, Michael (IS) wrote: > > You're getting closeif you don't use a field in a table you > don't HAVE to create it. > > Also...if you want to make your database a bit more bullet proof you > want

[sqlite] Error 11 - Database disk image is malformed

Hey guys. I have this table: eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo FLOAT) (I also happen to have this index: CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC)) This query works fine: SELECT * FROM eloResultTable This query returns

Re: [sqlite] Error 11 - Database disk image is malformed

); Thanks, Ian On 08/09/2011 14:29, Simon Slavin wrote: On 8 Sep 2011, at 2:22pm, Ian Hardingham wrote: This query works fine: SELECT * FROM eloResultTable This query returns Error 11 - Database disk image is malformed SELECT * FROM eloResultTable ORDER BY elo DESC Please run "PRAGMA integrity_

[sqlite] Getting the location of a record in an Index

Hey guys. (Thanks, I got my previous problem sorted). Again, I have: eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo FLOAT) with: CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC) If I have the id of a row in eloResultTable, I wish to find how

Re: [sqlite] Getting the location of a record in an Index

Hey Igor, thanks for the reply. Is this O(1)? Or... I guess it's probably low-magnitude O(log n) ? Ian On 09/09/2011 13:21, Igor Tandetnik wrote: Ian Hardingham<i...@omroth.com> wrote: Again, I have: eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo

Re: [sqlite] Getting the location of a record in an Index

Thanks Igor. I really want O(1), but selecting by elo DESC and then setting a ranking column for all records seems to be very slow, even during a transaction. Any tips for doing that fast? Thanks, Ian On 09/09/2011 13:34, Igor Tandetnik wrote: Ian Hardingham<i...@omroth.com> wrote

Re: [sqlite] Getting the location of a record in an Index

Hey Igor, thanks for the reply. Is this O(1) ? Ian On 09/09/2011 13:21, Igor Tandetnik wrote: Ian Hardingham<i...@omroth.com> wrote: Again, I have: eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo FLOAT) with: CREATE INDEX IF NOT EXISTS eloResul

[sqlite] Error 14 - cannot open Database

Hey guys. Woke up this morning to find my server unable to open our database file. Is there anything I can do to diagnose or repair it? We have backups but it would be good if it were possible to repair this one. Thanks, Ian ___ sqlite-users

Re: [sqlite] Error 14 - cannot open Database

Thanks for the reply Stephan. It transpires that the problem was not enough free hard drive space to create the journal file. Would be good if this was reported more verbosely. Thanks again, Ian On 09/10/2011 11:09, Stephan Beal wrote: On Sun, Oct 9, 2011 at 11:22 AM, Ian Hardingham&l

[sqlite] Accessing an sqlite db from two different programs

Hey guys, If I have program 1 and program 2 which both open the same db file, but they never write to the same table (but might be reading one written by another), do I need to do a lot of locking? I'm not worried about race conditions. Thanks, Ian

[sqlite] columnb = upper(columna)

Hey guys. I'm just getting around to this. Can I do: ALTER TABLE userTable ADD upperName = upper(name) TEXT Will this retroactively and for all future inserts work? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Accessing an sqlite db from two different programs

Hey guys, If I have program 1 and program 2 which both open the same db file, but they never write to the same table (but might be reading one written by another), do I need to do a lot of locking? I'm not worried about race conditions. Thanks, Ian

[sqlite] Query critique

Hey guys. I have a query which is very slow, and was wondering if there was any advice you guys had on it. Here are two table definitions: CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, record TEXT);

[sqlite] Query critique

Hey guys. I have a query which is very slow, and was wondering if there was any advice you guys had on it. Here are two table definitions: CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, record TEXT);

Re: [sqlite] cast from pointer to integer of different size

You know, compilers are like women - they warn you about a lot of things you really don't need to worry about. On 16/09/2010 12:18, Dan Kennedy wrote: > On Sep 16, 2010, at 2:09 AM, jagjeet singh nain wrote: > >> Hi, >> I was compiling sqlite on 64 bit OS and i got following warning >>

[sqlite] Sending a bunch of rows as a file

Hey guys. I have a master server which uses SQLite. Clients connect to the master server. The clients also use SQLite databases but they are not in general similar the the server db. However, there is a table which has the same definition on the server and client. I *do not* wish to sync

[sqlite] Keeping a table ordered

Hey guys. I have the following table: infPlayTable (id INTEGER PRIMARY KEY AUTOINCREMENT, infId INTEGER, name TEXT NOT NULL UNIQUE, score REAL) I often need to do the following: SELECT name, score FROM infPlayTable WHERE infId = 670 ORDER BY score DESC What is the syntax for the index I

Re: [sqlite] Keeping a table ordered

Great, thanks Simon. Just how fast will my Select be? Will it be order(n) with the number of records being returned? Thanks, Ian On 22/09/2010 12:32, Simon Slavin wrote: > On 22 Sep 2010, at 11:22am, Ian Hardingham wrote: > >> I have the following table: >> >>

[sqlite] Getting the next row

Hey guys. If I have a table with a PRIMARY KEY AUTOINCREMENT id column, and I have an id for a row, is there an Order(1) method of selecting the next row? I can't necessarilly guarantee that id + 1 exists, as it may be deleted. Thanks, Ian ___

[sqlite] Slow operation

Hey guys. My apologies in advance if this is a slightly mundane question. I'm running this code from a scripting language bound to SQLite: %r = db.query("SELECT * FROM userTable", 0); %i = 0; db.query("BEGIN TRANSACTION", 0); while (%i < db.numRows(%r)) {

Re: [sqlite] Slow operation

e: > Quoth Ian Hardingham<i...@omroth.com>, on 2010-10-05 11:52:36 +0100: >>Hey guys. My apologies in advance if this is a slightly mundane question. > (Please don't start new threads by replying to random messages. The > resultant header information indicates falsely that y

Re: [sqlite] Slow operation

ppreciated. Ian On 05/10/2010 12:22, Drake Wilson wrote: > Quoth Ian Hardingham<i...@omroth.com>, on 2010-10-05 12:16:11 +0100: >> Your query, >> >> UPDATE userTable SET playedInfIds = '' >> >> Still took two seconds actually... but significantly better than w

Re: [sqlite] Slow operation

Thanks again Drake, I'll investigate those alternatives. On 05/10/2010 13:52, Drake Wilson wrote: > Quoth Ian Hardingham<i...@omroth.com>, on 2010-10-05 12:27:38 +0100: >> CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL >> UNIQUE, password TEXT NOT

Re: [sqlite] Slow operation

Hey Jay, thanks for your feedback. I am indeed using (several) delineated lists. I would very much appreciate your input into how bad a decision this is. So, I basically need to find entrys of Table B that do not appear in that list. Obviously, it would be better to have a playedInf table

[sqlite] Incrementing a counter vs adding a row

Hey guys. If I wish to log how often a user does action x, I'm assuming I would be best off doing something like: /SELECT whatever FROM actionPerfomedTable WHERE user = y, action = x LIMIT 1/ If a record is returned, perform /UPDATE actionPerfomedTable SET number = incremented number WHERE

[sqlite] Query critique

Hey guys. I'm kind of revisiting something I asked about before. I have a high scores table, and a table of friends, and I wish to select for user x: The score of x The scores of all of x's friends Ordered by score descending. I am using this: SELECT * FROM (SELECT * FROM cupPlayTable

Re: [sqlite] Query critique

, but it seems like this is intermediate data SQLite might be able to get quicker? On 18/10/2010 16:12, Simon Slavin wrote: > On 18 Oct 2010, at 4:09pm, Ian Hardingham wrote: > >> I also want to add selecting the highest score, and adding it to the >> results assuming it isn't al

Re: [sqlite] Question about SQLite features.

Haha! Sqlite is embedded by others. It NEVER embeds. - Original message - > > On Nov 10, 2010, at 11:05 AM, Andy Gibbs wrote: > > > > That's I don't know SQLite have stored procedure support? > > > > > > > How're your C skills? > > Or perhaps SQLite should embed Lua [1] as its

[sqlite] Query help - two similar queries

Hey guys. I have a badly designed structure for a table which records /games played/ by people. It looks like: id player1 player2 score If score > 0, player 1 won the game. If score < 0, player 2 won it. (Score of 0 is a draw). I wish to find the total record in games between two specific

Re: [sqlite] Query help - two similar queries

wrote: > Ian Hardingham<i...@omroth.com> wrote: >> I have a badly designed structure for a table which records /games >> played/ by people. It looks like: >> >> id >> player1 >> player2 >> score >> >> If score> 0, player 1 won

Re: [sqlite] Query help - two similar queries

Many thanks again Igor. On 16/11/2010 13:15, Igor Tandetnik wrote: > Ian Hardingham<i...@omroth.com> wrote: >> Thanks Igor. Can i get custom results like >> >> GamesWonByPlayer1 >> >> By using getColumn in the normal way? > I'm not familiar with t

Re: [sqlite] How SQLite manages result of a SELECT query?

That seems like kind of a broad question. On 13/01/2011 10:33, Sunil Bhardwaj wrote: > Hi > > Please help us to understand, how SQLite manages result of a SELECT query: > - We are using in-memory db, > - > - When we do "execQuery", what operations are internally done in SQLite >

[sqlite] Query or table

Hey guys. I am currently doing the following to find out the "record" between two players in my game: SELECT count(*) TotalGames, sum(score > 0) GamesWonByPlayer1, sum(score < 0) GamesWonByPlayer2, sum(score = 0) Draws FROM multiturnTable WHERE complete=1 AND player1='Johnson' AND

Re: [sqlite] Query or table

xpectation that that would not be especially faster than my current method? Thanks, Ian On 18/01/2011 14:07, Philip Graham Willoughby wrote: > On 18 Jan 2011, at 13:51, Ian Hardingham wrote: > >> Hey guys. I am currently doing the following to find out the "record" &

[sqlite] Date operations when date is a TEXT

Hey guys. Probably unwisely, I store dates in the following format: "year month day hour minute" For example: "11 1 4 16 22" I wish to find all rows in a table which are more than 8 days old. Is there a way of doing this in SQLite or should I just do it in my own code? Thanks, Ian

Re: [sqlite] Query or table

Many thanks for the advice Phil, I'll follow it. Ian On 19/01/2011 13:06, Philip Graham Willoughby wrote: > Ian, > > On 18 Jan 2011, at 16:40, Ian Hardingham wrote: > >> In general, my server is too slow. It has to run many operations a >> second, and many DB operations,

[sqlite] Joinery

Hey guys. This is just an utterly simple question I know, but I still haven't got my head around it. I have two tables: Table A int ID Table B int user int aID I need a query which selects all elements of Table A which are "owned" by a specific user, ie for which there is an entry with

Re: [sqlite] Joinery

Works perfectly, many thanks Martin. Ian On 21/01/2011 11:49, Martin.Engelschalk wrote: > select a.ID > from a > join b on b.aID = a.ID > where b.user = 'MyUser' ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Query question

Hey guys. I have the following table: ratingsTable (id INTEGER PRIMARY KEY AUTOINCREMENT, mtId INTEGER, user TEXT, rating INTEGER); mtId links to another table's primary key I wish to have a query which gives me the mtId which is represented most often in the ratingsTable. Does anyone have

Re: [sqlite] Query question

Great, many thanks guys. Ian On 25/01/2011 14:59, Igor Tandetnik wrote: > Ian Hardingham<i...@omroth.com> wrote: >> ratingsTable (id INTEGER PRIMARY KEY AUTOINCREMENT, mtId INTEGER, user >> TEXT, rating INTEGER); >> >> I wish to have a query which gives me t

[sqlite] Multiple threads sharing one DB

Hey guys. I am under the impression that there is no concurrent access to a single SQLite DB. Ie if thread A is performing a query, and thread B trys to query, it will block until thread A is finished, no matter the query. 1. Is this correct? 2. Are there any fairly general workarounds of

Re: [sqlite] Multiple threads sharing one DB

Many thanks Eric. Does a write on Table A block a read/write on Table B? On 26/01/2011 16:18, Eric Smith wrote: > > > On Wed, Jan 26, 2011 at 11:02 AM, Ian Hardingham <i...@omroth.com > <mailto:i...@omroth.com>> wrote: > > Hey guys. >

[sqlite] Multiple threads sharing one DB

Hey guys. I am under the impression that there is no concurrent access to a single SQLite DB. Ie if thread A is performing a query, and thread B trys to query, it will block until thread A is finished, no matter the query. 1. Is this correct? 2. Are there any fairly general workarounds of

[sqlite] "Next" record

Hey guys. I wish to get the "next" record of a certain type, such that if we have row Id x, then: Select the minimum id such that id > x (with some WHERE conditions) If there is no such id (ie x is the largest with the conditions) then the first id with those conditions is returned. I'm doing

Re: [sqlite] "Next" record

Great, thanks Igor. On 31/01/2011 14:45, Igor Tandetnik wrote: > Ian Hardingham<i...@omroth.com> wrote: >> I wish to get the "next" record of a certain type, such that if we have >> row Id x, then: >> >> Select the minimum id s

[sqlite] Optimising a bad design decision

Hey guys. First off, thanks to all who have helped me in the recent weeks. We're in crunch on my project and my rather complicated server, combined with my lack of DB experience, has given me plenty of problems to deal with. My core users table has a user defined by a string which is their

Re: [sqlite] Optimising a bad design decision

Hi Igor, thankyou. If I wish to make this modification now, what steps would I need to take? And in your opinion what % of the optimisation of doing it with integers would this provide? Thanks, Ian On 01/02/2011 16:19, Igor Tandetnik wrote: > On 2/1/2011 10:10 AM, Ian Hardingham wrote: &g

Re: [sqlite] Optimising a bad design decision

Many thanks Puneet and Igor - I will do those things. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Yet another question - this time about using two tables

I have a table called multiturnTable which records games between two players, so has two fields "player1" and "player2". Currently when, for instance, trying to find all games involving a specific player, I search based on player1=x OR player2=x. I'm fairly sure this is anti-good db design.

[sqlite] Is this SQLite implementation doing something stupid?

Hey guys. I'm using an SQLite implementation that someone else made for my high-level language of choice. While looking through the imp, I've just found this function, which is used as the callback argument to sqlite3_exec. Does this look like an ok useage? It seems to me like this might be

[sqlite] Surprising profiling results

I was attempting to optimise this query this weekend: SELECT * FROM multiturnTable WHERE (player1 LIKE '?' OR player2 LIKE '?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0; multiturnTable has about 70,000 rows and has no explicit indexes. I was

Re: [sqlite] Surprising profiling results

Hi Igor, Wow - changing to that in combination with indexes on player1 and player2 has dropped the time to 25 and 10 - an incredible improvement. I'll need to get my head around using combinations of queries which each only use indexed columns. Thanks, Ian On 08/02/2011 13:48, Igor Tandetnik

Re: [sqlite] Bi-directional unique

My apologies if this is stupid, or it's already been discussed. There's a way of choosing an ordering on anything, even strings. Have two tables - one where members of column A are "larger", one where members of column B are "larger". Only insert into the correct table (O(1) operation).

[sqlite] 1000 insert statements into empty table taking 20 seconds... very odd!

Hey guys - this is my first post here, apologies if I violate any etiquette. I have a table I create with: CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, record TEXT); I run a loop from a scripting

[sqlite] SQLite memory leakage

Hey guys. Under what circumstances should I need to call VACUUM? My server application seems to have a very variable memory footprint which I have tracked down to large SQLite SELECT results. Thanks, Ian ___ sqlite-users mailing list

Re: [sqlite] SQLite memory leakage

Thanks for the answers guys. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Optimising usage of LIKE

Hey guys. For various embarrassing reasons, I'm using: SELECT x FROM userTable WHERE name LIKE 'name' To look up entries in my account table. Basically, the scripting language I'm using which hooks into SQLite is a bit case-agnostic. I've been told by a friend that this is extremely

[sqlite] Optimising usage of LIKE

Hey guys. For various embarrassing reasons, I'm using: SELECT x FROM userTable WHERE name LIKE 'name' To look up entries in my account table. Basically, the scripting language I'm using which hooks into SQLite is a bit case-agnostic. I've been told by a friend that this is extremely

[sqlite] Suggestions on optimising this

Hey guys. I have an 1000 row table that looks like this: CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, record TEXT); And a "friends" table which looks like this: CREATE TABLE IF NOT EXISTS friendTable

Re: [sqlite] Suggestions on optimising this

from globalRankingTable a, friendTable b > where b.current_user = ?1 > and b.friend = a.name; > > > Pavel > > On Thu, May 6, 2010 at 5:35 AM, Ian Hardingham <i...@omroth.com> wrote: > >> Hey guys. >> >> I have an 1000 row table that looks like this: >

[sqlite] Select question

Hey guys. I have the following query: SELECT a.* FROM dailyRankingTable a, friendTable b WHERE upper(b.player) = upper('?') AND upper(b.friend) = upper(a.name) (ignore the uppers for now - I'm going to refactor soon) I would like this query to also select the first 10 elements of