Differences

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

--- computer_science:databases:mysql:mysql_tips_trics [2021/03/07 16:56] – created carlossousa
+++ computer_science:databases:mysql:mysql_tips_trics [2023/12/01 12:07] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-======   MySQL Tips & Tricks   ======
+====== MySQL Tips & Tricks ======
 Part of [[: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>