Differences

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

--- computer_science:databases:mysql:mysql_tips_trics [2021/03/07 16:59] – carlossousa
+++ computer_science:databases:mysql:mysql_tips_trics [2023/12/01 12:07] (current) – external edit 127.0.0.1
@@ Line 5: / Line 5: @@
 ===== Use Indexes =====
-Pros: Massive increase in searching performance.
+**Pros:** Massive increase in searching performance.
-Cons: Slightly Increase in Storage Requirements. Slightly Increase on Write speeds.
+**Cons:** Slightly Increase in Storage Requirements. Slightly Increase on Write speeds.
-Usage:
+**Example:**
 <code>
-ALTER TABLE table_name ADD INDEX index_Name (column);
+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>