On 2015-12-11 02:28, ??? wrote:
> The first time your reading from Disk, the 2nd and subsequent times you > reading from cache > > HI,Hick Gunter, > Thanks for you reply. > I find a strange problem. > For example, tableA contains two columns: implicit rowid, A_id. > we create index on A_id. > firstly, we used rowid to select the row, cost about 400 seconds; > secondly, we used A_id to select the row, cost about 200 seconds; > thirdly, we also used rowid to select the row, cost about 200 seconds. > > if firstly,we used A_id to select the row, cost about 200 seconds; > secondly, we also used rowid to select the row, cost about 200 seconds. > > the create index on A_id may have some influence to do with the rowid. and > from this example, I also think the rowid is not implicit index. > what do you think about this phenomenon? > > best regards! > > At 2015-12-11 15:15:16, "Hick Gunter" <hick at scigames.at> wrote: > >> There is always an implicit index on the SQLite rowid and this is the >> fastest method to locate a row. The next best thing for retrieval speed is >> an index that matches the where clause. If you do not have one, SQLite may >> decide to create a temporary index anyway, but this depends on the query. >> Unless you have what is called a "covering index" (i.e. all fields mentioned >> in the query are present in the index), SQLite will retrieve a rowid from >> the index and use that to read the row from the table. Your second method >> uses a full table scan, i.e. SQLite is forced to retrieve every single row >> of the table to check i fit matches, which ist he slowest access method >> -----Urspr?ngliche Nachricht----- Von: sqlite-users-bounces at >> mailinglists.sqlite.org [mailto:sqlite-users-bounces at >> mailinglists.sqlite.org] Im Auftrag von ??? Gesendet: Freitag, 11. Dezember >> 2015 07:40 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] >> ABOUT ROWID Hi , Everyone, When I use methodone : select * from table_name where rowid = somenumber ? When I use methodtwo : select * from table_name where ordinaryid = somenumber ? (ordinary is not index) The table_name have at least two columns. I have some questions, as follows: 1.The methodone is faster than the methodtwo? 2.is rowid the default index? best wishes! _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users [1] ___________________________________________ 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 [1] > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users [1] Links: ------ [1] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
