The type (type affinity) with one exception makes no real difference.
The exception being specifically the_column_name INTEGER PRIMARY KEY (with or without AUTOINCREMENT) which defines the column as an alias of the rowid column. INT PRIMARY KEY does not.
so the_column_name CHAR_INT PRIMARY KEY, the_column_name INT CHAR PRIMARY KEY or even the_column_name INT PRIMARY KEY are actually the same, the_column_name RUMPLESTILTSKIN PRIMARY KEY could even be used (although the latter would have a different type affinity).
It is the rules that determine the type affinity. There are 5. The rule which has the highest precedence is if the type has INT then the type affinity is INTEGER. RUMPLESTILTSKIN as a type drops through all rules except the last that is if none of the previous rules apply then the type affinity is NUMERIC.
3.1. Determination Of Column Affinity
The affinity of a column is determined by the declared type of the column, according to the
following rules in the order shown:
If the declared type contains the string "INT" then it is assigned
INTEGER affinity.
If the declared type of the column contains any of the strings "CHAR",
"CLOB", or "TEXT" then that column has TEXT affinity. Notice that the
type VARCHAR contains the string "CHAR" and is thus assigned TEXT
affinity.
If the declared type for a column contains the string "BLOB" or if no
type is specified then the column has affinity BLOB.
If the declared type for a column contains any of the strings "REAL",
"FLOA", or "DOUB" then the column has REAL affinity.
Otherwise, the affinity is NUMERIC.
Note that the order of the rules for determining column affinity is
important. A column whose declared type is "CHARINT" will match both
rules 1 and 2 but the first rule takes precedence and so the column
affinity will be INTEGER.
Datatypes In SQLite
Saying that the type affinity does not determine how the data is stored. Each and every column is stored according to the storage class that is dependant upon the data being stored.
Null is stored as a null, a string of numerics (enclosed or not as a string) as an integer. In short the data will be stored as SQLite determines and SQlite will try to store the data as efficiently as it can and in as little space as it can down to a byte being the smallest unit that is stored.
Consider the following :-
DROP TABLE IF EXISTS mytable1;
DROP TABLE IF EXISTS mytable2;
DROP TABLE IF EXISTS mytable3;
CREATE TABLE IF NOT EXISTS mytable1 (c1 CHAR_INT PRIMARY KEY);
CREATE TABLE IF NOT EXISTS mytable2 (c1 INT PRIMARY KEY);
CREATE TABLE IF NOT EXISTS mytable3 (c1 RUMPLEstiltSkin PRIMARY KEY);
-- INSERT INTO mytable1 VALUES(12345),('12345'),('a_12345'),('1_12345'),(x'0102030405'); -- fails due to unique constraint 12345 and '12345' are the same
-- INSERT INTO mytable2 VALUES(12345),('12345'),('a_12345'),('1_12345'),(x'0102030405'); -- fails due to unique constraint 12345 and '12345' are the same
-- INSERT INTO mytable3 VALUES(12345),('12345'),('a_12345'),('1_12345'),(x'0102030405'); -- fails due to unique constraint 12345 and '12345' are the same
INSERT INTO mytable1 VALUES(12345),('54321'),('a_12345'),('1_12345'),(x'0102030405');
INSERT INTO mytable2 VALUES(12345),('54321'),('a_12345'),('1_12345'),(x'0102030405');
INSERT INTO mytable3 VALUES(12345),('54321'),('a_12345'),('1_12345'),(x'0102030405');
SELECT c1, typeof(c1) FROM mytable1;
SELECT c1, typeof(c1) FROM mytable2;
SELECT c1, typeof(c1) FROM mytable3;
- The commented out INSERTS (if uncommented and run) fail with a UNIQUE conflict as SQLite considers 12345 the same as '12345'.
the typeof function returns the type of the column (storage type NOT column affinity)
The results come up as :-
Using anything but INTEGER PRIMARY KEY (there are some derivations) and thus an alias of the rowid is
- about half as fast
has two indexes, the rowid (unless the TABLE is defined as WITHOUT ROWID) and the PRIMARY KEY.
Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. ROWIDs and the INTEGER PRIMARY KEY
handling numbers as opposed to strings will consume more space and therefore will reduce what data can be held in a buffer thus there would be some impact.
Searching an index, is relatively fast, there is, in comparison to the data itself, relatively little data and the data itself is only read.
Perhaps consider the following :-
DROP TABLE IF EXISTS mytable1;
DROP TABLE IF EXISTS mytable2;
DROP TABLE IF EXISTS mytable3;
CREATE TABLE IF NOT EXISTS mytable1 (pk INT PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS mytable2 (pk CHAR_INT PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS mytable3 (pk INT PRIMARY KEY, name TEXT) WITHOUT ROWID;
INSERT INTO mytable1
WITH RECURSIVE cte1(a,b) AS (
SELECT 'ABC_'||CAST(abs(random()) AS TEXT),'some data' UNION ALL
SELECT DISTINCT (substr(a,1,4))||CAST(abs(random()) AS TEXT),'some data' FROM cte1 LIMIT 1000000
)
SELECT * FROM cte1
;
INSERT INTO mytable2
WITH RECURSIVE cte1(a,b) AS (
SELECT '1_'||CAST(abs(random()) AS TEXT),'some data' UNION ALL
SELECT DISTINCT (abs(random()) % 100)||'_'||CAST(abs(random()) AS TEXT),'some data' FROM cte1 LIMIT 1000000
)
SELECT * FROM cte1
;
INSERT INTO mytable3 SELECT * FROM mytable1;
SELECT * FROM mytable1 WHERE name LIKE('%me data%');
SELECT * FROM mytable2 WHERE name LIKE('%me data%');
SELECT * FROM mytable3 WHERE name LIKE('%me data%');
SELECT * FROM mytable3 WHERE name LIKE('%me data%');
SELECT * FROM mytable1 WHERE name LIKE('%me data%');
SELECT * FROM mytable2 WHERE name LIKE('%me data%');
SELECT * FROM mytable2 WHERE name LIKE('%me data%');
SELECT * FROM mytable3 WHERE name LIKE('%me data%');
SELECT * FROM mytable1 WHERE name LIKE('%me data%');
This creates 3 permutations all with 1,000,000 rows
mytable1 has the primary key as ABC_???? e.g. :-
mytable2 has the primary key as ??_???? e.g. :-
mytable3 is a copy of mytable1 BUT the table has been defined using WITHOUT ROWID
Timings
The SELECTS for all 3 are pretty close (multiple selects done and in different order to even out caching). The messages which include the timings are :-
SELECT * FROM mytable1 WHERE name LIKE('%me data%')
> OK
> Time: 0.672s
SELECT * FROM mytable2 WHERE name LIKE('%me data%')
> OK
> Time: 0.667s
SELECT * FROM mytable3 WHERE name LIKE('%me data%')
> OK
> Time: 0.702s
SELECT * FROM mytable3 WHERE name LIKE('%me data%')
> OK
> Time: 0.7s
SELECT * FROM mytable1 WHERE name LIKE('%me data%')
> OK
> Time: 0.675s
SELECT * FROM mytable2 WHERE name LIKE('%me data%')
> OK
> Time: 0.673s
SELECT * FROM mytable2 WHERE name LIKE('%me data%')
> OK
> Time: 0.676s
SELECT * FROM mytable3 WHERE name LIKE('%me data%')
> OK
> Time: 0.709s
SELECT * FROM mytable1 WHERE name LIKE('%me data%')
> OK
> Time: 0.676s
- I believe mytable3, takes a bit of a hit because the scan (in this case) is on the PRIMARY KEY, rather than the rowid which is suitable/preferable for the other two.
In addition to the previous links, You may also wish to look at :-
