When it revert back after dropping the index. The speed does not become slower.
At 2015-12-14 21:35:03, "Hick Gunter" <hick at scigames.at> wrote:
>Does it revert back to slower speed after dropping the index?
>Can you compare the EXPLAIN output produced with and without the index?
>
>There is no difference on my machine (Version 3.7.14); if yours behaves the
>same way then whatever changes speed is definitely not because SQLite is doing
>something differently.
>
>asql> create temp table t (x integer primary key asc, y,z);
>asql> .explain
>asql> explain select * from t where rowid=15;
>addr opcode p1 p2 p3 p4 p5 comment
>---- ------------- ---- ---- ---- ------------- -- -------------
>0 Trace 0 0 0 00 NULL
>1 Integer 15 1 0 00 NULL
>2 Goto 0 12 0 00 NULL
>3 OpenRead 0 2 1 3 00 t
>4 MustBeInt 1 10 0 00 NULL
>5 NotExists 0 10 1 00 pk
>6 Rowid 0 3 0 00 NULL
>7 Column 0 1 4 00 t.y
>8 Column 0 2 5 00 t.z
>9 ResultRow 3 3 0 00 NULL
>10 Close 0 0 0 00 NULL
>11 Halt 0 0 0 00 NULL
>12 Transaction 1 0 0 00 NULL
>13 VerifyCookie 1 1 0 00 NULL
>14 TableLock 1 2 0 t 00 NULL
>15 Goto 0 3 0 00 NULL
>asql> explain select * from t where x=15;
>addr opcode p1 p2 p3 p4 p5 comment
>---- ------------- ---- ---- ---- ------------- -- -------------
>0 Trace 0 0 0 00 NULL
>1 Integer 15 1 0 00 NULL
>2 Goto 0 12 0 00 NULL
>3 OpenRead 0 2 1 3 00 t
>4 MustBeInt 1 10 0 00 NULL
>5 NotExists 0 10 1 00 pk
>6 Rowid 0 3 0 00 NULL
>7 Column 0 1 4 00 t.y
>8 Column 0 2 5 00 t.z
>9 ResultRow 3 3 0 00 NULL
>10 Close 0 0 0 00 NULL
>11 Halt 0 0 0 00 NULL
>12 Transaction 1 0 0 00 NULL
>13 VerifyCookie 1 1 0 00 NULL
>14 TableLock 1 2 0 t 00 NULL
>15 Goto 0 3 0 00 NULL
>asql> create index t_x on t(x);
>asql> explain select * from t where x=15;
>addr opcode p1 p2 p3 p4 p5 comment
>---- ------------- ---- ---- ---- ------------- -- -------------
>0 Trace 0 0 0 00 NULL
>1 Integer 15 1 0 00 NULL
>2 Goto 0 12 0 00 NULL
>3 OpenRead 0 2 1 3 00 t
>4 MustBeInt 1 10 0 00 NULL
>5 NotExists 0 10 1 00 pk
>6 Rowid 0 3 0 00 NULL
>7 Column 0 1 4 00 t.y
>8 Column 0 2 5 00 t.z
>9 ResultRow 3 3 0 00 NULL
>10 Close 0 0 0 00 NULL
>11 Halt 0 0 0 00 NULL
>12 Transaction 1 0 0 00 NULL
>13 VerifyCookie 1 2 0 00 NULL
>14 TableLock 1 2 0 t 00 NULL
>15 Goto 0 3 0 00 NULL
>
>-----Urspr?ngliche Nachricht-----
>Von: sqlite-users-bounces at mailinglists.sqlite.org
>[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von ???
>Gesendet: Montag, 14. Dezember 2015 14:06
>An: SQLite mailing list
>Betreff: [sqlite] Is rowid the fastest?
>
>You said that "You are probably falling into the cache effect trap again.
>There is no point in indexing on the primary key, it only wastes space and CPU
>cycles ".
> I do not agree with you. let me tell you why.
> Before I retrieve by index which created for primary key. The speed of
> retrieve by rowid is very stable(for a long time).
> Once I retrieve by index , the stable will broken and the speed is faster
> than its before.
> I think you could try it.
>
>
>
>
>
>___________________________________________
> Gunter Hick
>Software Engineer
>Scientific Games International GmbH
>FN 157284 a, HG Wien
>Klitschgasse 2-4, A-1130 Vienna, Austria
>Tel: +43 1 80100 0
>E-Mail: hick at scigames.at
>
>This communication (including any attachments) is intended for the use of the
>intended recipient(s) only and may contain information that is confidential,
>privileged or legally protected. Any unauthorized use or dissemination of this
>communication is strictly prohibited. If you have received this communication
>in error, please immediately notify the sender by return e-mail message and
>delete all copies of the original communication. Thank you for your
>cooperation.
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
