====== MySQL Tips & Tricks ======

Part of [[engineering:computer_science:databases:mysql|MySQL]]

===== Use Indexes =====

**Pros:** Massive increase in searching performance.

**Cons:** Slightly Increase in Storage Requirements. Slightly Increase on Write speeds.

**Example:**
<code>

ALTER TABLE table_name ADD INDEX index_name (column);

</code>

===== Enforce Data Integrity at the DB Level =====

**Eg.: **unique constraints, cascade deletes

**Pros: **Application agnostic Data Integrity

**Cons:** -
===== Log Queries in Development =====

**Pros: **Great to see Queries happening in Real Time

**Cons: **Slightly higher storage requirement

Don't forget to disable in Production

**Usage:**

Update my.cnf and add / change:

<code>
general_log_file = /var/log/mysql_queries.log
general_log = 1

</code>

check log with:

<code>
tail -f /var/log/mysql_queries.log

</code>
===== Use Transactions for bulk insert: =====

**Pros:** Only one re-index, at the end of the transaction.

**Cons: **Visibility of new data only after commit - this can be considered an upside -.

**Usage:**
<code>
START transaction;
insert into table values (default, 1);
insert into table values (default, 2);
insert into table values (default, 3);
insert into table values (default, 4);
insert into table values (default, 5);
COMMIT;

</code>