Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
computer_science:databases:mysql:mysql_tips_trics [2021/03/07 17:02] – 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 ===== | ===== 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:** |
< | < | ||
Line 15: | Line 15: | ||
</ | </ | ||
+ | |||
===== Enforce Data Integrity at the DB Level ===== | ===== Enforce Data Integrity at the DB Level ===== | ||
- | Pros: Application agnostic Data Integrity | + | **Eg.: **unique constraints, |
- | 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: | ||
< | < | ||
- | posts table | + | general_log_file = / |
- | - post_id | + | general_log = 1 |
- | - title | + | |
- | - author_name | + | |
</ | </ | ||
- | Do: | + | check log with: |
< | < | ||
- | posts table | + | tail -f / |
- | - post_id | + | |
- | - title | + | |
- | - author_id | + | |
- | authors table | + | </ |
- | - 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 | ||
+ | |||
+ | **Usage: | ||
+ | < | ||
+ | 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; | ||
</ | </ | ||