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
computer_science:databases:mysql:mysql_tips_trics [2021/03/07 17:04] – [Enforce Data Integrity at the DB Level] carlossousacomputer_science:databases:mysql:mysql_tips_trics [2023/12/01 12:07] (current) – external edit 127.0.0.1
Line 11: Line 11:
 **Example:** **Example:**
 <code> <code>
 +
 ALTER TABLE table_name ADD INDEX index_name (column); ALTER TABLE table_name ADD INDEX index_name (column);
  
 </code> </code>
- 
  
 ===== Enforce Data Integrity at the DB Level ===== ===== Enforce Data Integrity at the DB Level =====
Line 23: Line 23:
  
 **Cons:** - **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:
  
-**Instead of:** 
 <code> <code>
-posts table +general_log_file = /var/log/mysql_queries.log 
-- post_id +general_log = 1
-- title +
-- author_name+
  
 </code> </code>
  
-**Do:**+check log with: 
 <code> <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> </code>
  
  
  • computer_science/databases/mysql/mysql_tips_trics.1615136670.txt.gz
  • Last modified: 2023/12/01 12:07
  • (external edit)