当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - How are indexes effected when storing a date as text rather than integer in SQLite?

问题描述:

SQLite states that data can be represented as either Unix timestamps or dates. Text is much more readable but integers takes up less row data and index data.

http://www.sqlite.org/datatype3.html

CREATE TABLE testDate (a DATETIME);

INSERT INTO testDate VALUES('1999-12-31 15:00:00');

INSERT INTO testDate VALUES(946652400); -- We will store one format or the other (not both)

SELECT typeof ( a ) FROM testDate;

'text'

'integer'

Our queries will be like

SELECT * FROM testDate

WHERE a > '1990-12-31 15:00:00'

Or

SELECT * FROM testDate

WHERE a > 926652400

Will the index on a text date still work and will there be any significant performance loss?

网友答案:

Indexes on text values work.

There might be a measurable performance difference, but whether this difference is noticeable, and whether this difference is significant four your particular application, depends on how much data you have, how fast your storage is, and how many queries you run. You will have to measure this yourself.

分享给朋友:
您可能感兴趣的文章:
随机阅读: