MySQL

Asked this question to GPT with reasoning:

Me:
-----
What is MySQL without a storage engine?
1. Give me one-word answer.
2. Give me explanation.

Answer:
-----
Parser

Without a storage engine, MySQL loses its core component that actually manages
data—handling storage, retrieval, indexing, and transactions.
What remains is essentially just the SQL parser and optimizer,
which can only interpret and plan queries but can’t execute them to store or fetch data.

So I found out that we can use different types of storage engine with MySQL: [InnoDB (default), CSV, MEMORY (in-memory tables), MyISAM (older engine)].

What I did was to create a table using CSV engine and InnoDB and tried to add index for that, you will see that for CSV it will fail because the CSV storage engine has a hardcoded maximum of 0 indexes.

Let’s first spin up dummy MySQL container using docker

docker run --name mysql-csv-test -e MYSQL_ROOT_PASSWORD=root -d mysql:latest
docker exec -it mysql-csv-test mysql -uroot -proot

-- Table created with CSV Engine.
mysql> CREATE TABLE users (
    ->     id INT NOT NULL,
    ->     name VARCHAR(100) NOT NULL,
    ->     age INT NOT NULL
    -> ) ENGINE=CSV;
Query OK, 0 rows affected (0.02 sec)
 
 
mysql> INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);
Query OK, 1 row affected (0.00 sec)
-- other inserts
 
mysql> SELECT * FROM users;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | Alice   |  25 |
|  2 | Bob     |  30 |
|  3 | Charlie |  22 |
+----+---------+-----+
3 rows in set (0.01 sec)
 
mysql> CREATE INDEX idx_name ON users(name);
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

-- Table created with InnoDB Engine.
mysql> CREATE TABLE users_inno (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(100) NOT NULL,
    ->     age INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
 
mysql> mysql> INSERT INTO users_inno (name, age) VALUES ('Alice', 25);
Query OK, 1 row affected (0.00 sec)
-- other inserts
 
mysql> SELECT * FROM users_inno;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | Alice   |  25 |
|  2 | Bob     |  30 |
|  3 | Charlie |  22 |
+----+---------+-----+
3 rows in set (0.00 sec)
 
mysql> CREATE INDEX idx_name ON users_inno(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Want to see actual files being stored in the container?

docker exec -it mysql-csv-test bash
cd /var/lib/mysql/test_db
ls -lah
 
 
## Example output in my case
bash-5.1# cd /var/lib/mysql/test_db
ls -lah
total 148K
drwxr-x--- 2 mysql mysql 4.0K Feb  9 10:21 .
drwxrwxrwt 8 mysql mysql 4.0K Feb  9 10:04 ..
-rw-r----- 1 mysql mysql   35 Feb  9 10:06 users.CSM
-rw-r----- 1 mysql mysql   39 Feb  9 10:06 users.CSV            # notice file size here
-rw-r----- 1 mysql mysql 3.7K Feb  9 10:06 users_372.sdi
-rw-r----- 1 mysql mysql 128K Feb  9 10:21 users_inno.ibd       # compare to CSV it's huge