engineering:computer_science:databases:mysql:mysql_tips_trics

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
engineering:computer_science:databases:mysql:mysql_tips_trics [2024/08/16 13:56] – removed - external edit (Unknown date) 127.0.0.1engineering:computer_science:databases:mysql:mysql_tips_trics [2024/08/16 13:56] (current) – ↷ Links adapted because of a move operation carlossousa
Line 1: Line 1:
 +====== 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>
 +