As in the question. What specification should have session id column in database. The type, character set etc.
The requirements are fastest query with stability, but some advantages during read of session from cookie etc could have important impact too.
Ok. I made some tests.
I performed a performance test using VARCHAR and CHAR columns. Both had the same size 64, SID length 32 bytes. On each refresh of the session, I inserted a loop where the session, instead of saving once, repeated it 200 times. I checked the speed of operation, i.e. session recording, and the order in the database using phpMyAdmin. I was interested in: redundancy, i.e. the useless space created in the database, the size of the index, the length of the row.
The results turned out to be surprising!
Speed doesn’t matter. It’s exactly the same! If there were time differences, they were exactly the same in both cases. That is: if at one time the writing process in the table with VARCHAR was 500ms (495-510), then exactly the same times were in the CHAR table. The range of times ranged from 460 to 510. The times tested were comparable in both cases. I will not post a link to the examined tables due to the risk of a DDOS attack.
The study lasted 5 days. Redundancy, as it turned out, depended on the number of deleted rows, it was created immediately after this process and persisted for some time.
It oscillated from 4KB and dropped even to 0 in both cases. Line length from several hundred bytes, e.g. 600 to 11KB.
Indexes was the same and depends on amount of row: from 4-7 KB and was bigger always from DATA. There was 3 INDEXES on primary key and two on other columns. All the same type one on timestamp type.
The study, however, was not complete. It involved a small amount of data. The number of rows did not exceed 40. Most often it was 6-15 rows. They were removed by a random function quite often.
Conclusions:
In my case, there were no differences, but as I wrote, the study was based on a small amount of data. Maybe with more rows, eg 10000, there would be some difference…
2
Answers
uuid or similar implementation is preferred as SessionId by many platform such as laravel, they use data type as Varchar(20), Word of caution is you are using mysql as you db it can cause performance issue if you set varchar/string as a primary key. Read More
Laravel Migration
The data type for hash function or uuid in MySQL depends on how you want to store and use them.
One option is to use VARCHAR(36) for both hash function and uuid12, which allows you to store them as human-readable strings. However, this may take more space than necessary and affect the performance of your queries.
Another option is to use BINARY(16) for uuid3, which allows you to store them as raw binary values. This may save some space and improve the performance of your queries, but it may also make them harder to read and manipulate.
A third option is to use BIGINT UNSIGNED for uuid4, which allows you to store them as 64-bit unsigned integers. This may also save some space and improve the performance of your queries, but it may also limit the range of possible values and require some conversion functions.
What are your requirements for storing and using session ids?