My radar says having a TEXT field as a primary key is bad (your userTable).
String compares are horrendously slow.
Michael D. Black
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: Friday, June 03, 2011 8:40 AM
Cc: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL?
Hey guys, thank you very much for the help so far.
The list of calls which I make during the "end match section", which can
take 2 seconds, is:
SELECT * FROM multiturnTable WHERE id=? LIMIT 1
UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?
UPDATE multiturnTable SET complete=1, score=? WHERE id=?
SELECT * FROM userTable WHERE name='?' twice
UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
scoreStreak='?', scoreStreakNumber=? WHERE name='?'; twice
The setup of the various tables are:
CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT
NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date
TEXT, rating REAL, info TEXT, complete INTEGER, currentTurn INTEGER,
p1Submitted INTEGER, p2Submitted INTEGER, score REAL, gamemode TEXT,
turnLimit INTEGER, turnTimeLimit INTEGER, p1SubmitScore INTEGER,
p2SubmitScore INTEGER, quickMatchId INTEGER, nComments INTEGER DEFAULT
0, p1TurnSubTime FLOAT, p2TurnSubTime FLOAT, nRatings INT, p1GivenUp
INT, p2GivenUp INT, dupId INT DEFAULT -1, p1Declined INT DEFAULT 0,
p2Declined INT DEFAULT 0, lastP1CommitTime TEXT DEFAULT '-1',
lastP2CommitTime TEXT DEFAULT '-1');";
Multiturn has about 200,000 rows at present
CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL
UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date
TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT
DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT
'0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER
DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0,
noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '');";
Usertable has about 40,000 rows at present.
I have the following indexes:
db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable
(player1)", 0);
db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer2 ON multiturnTable
(player2)", 0);
Thanks,
Ian
On 03/06/2011 13: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 finishing a match. This requires
>> an update to three separate tables.
> Send us the schema of the above tables and the SQL that you execute that
> takes 2 seconds.
>
> Tom
> BareFeetWare
>
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
