Differences

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

--- computer_science:databases:mysql:mysql_tips_trics [2021/03/07 17:03] – [Use Indexes] carlossousa
+++ computer_science:databases:mysql:mysql_tips_trics [2023/12/01 12:07] (current) – external edit 127.0.0.1
@@ Line 11: / Line 11: @@
 **Example:**
 <code>
 ALTER TABLE table_name ADD INDEX index_name (column);
 </code>
 ===== Enforce Data Integrity at the DB Level =====
-Pros: Application agnostic Data Integrity
+**Eg.: **unique constraints, cascade deletes
-Cons: -
+**Pros: **Application agnostic Data Integrity
-Instead of:
+**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>
-posts table
+general_log_file = /var/log/mysql_queries.log
-- post_id
+general_log = 1
-- title
-- author_name
 </code>
-Do:
+check log with:
 <code>
-posts table
+tail -f /var/log/mysql_queries.log
-- post_id
-- title
-- author_id
-authors table
+</code>
-- author_id
+===== Use Transactions for bulk insert: =====
-- name
+**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>