问题描述:

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.

相关阅读:
Top