New answers tagged

0

Sort the data by uuid before loading it -- this will speed up the load and shrink the disk space some (because of less fragmentation) Use InnoDB -- this will speed up the SELECTs. Pack the uuids into BINARY(16) -- this will shrink the disk space by perhaps a factor of 2. If uuid_tags_codes is just tiny numbers, use TINYINT UNSIGNED -- 1 byte instead of 4. (...


2

Let's do some calculation: uuid: 36 bytes (Add 1 because it is varchar) ==> 37 uuid_tags_codes: 4 bytes When indexed, full length of uuid field: 100 bytes (37+4+100)bytes * 162,281,544 rows = 21.3 G So, 21G without adding the overhead! Therefore, your first problem is the space. To answer your questions: It is more than 6-8G. To ba safe you need ~...


1

I found that sqlite3 custom init script can have meta-command as well as SQL statement: #!/bin/sh commandfile=$(mktemp) # create temporary init script cat <<EOF > $commandfile .mode csv tablename .import /dev/stdin tablename EOF # import bzip2 -d -c huge_compressed.csv.bz2 | sqlite3 --init $commandfile dbname.db



Top 50 recent answers are included