MySQL Tips & Tricks

Part of MySQL

Pros: Massive increase in searching performance.

Cons: Slightly Increase in Storage Requirements. Slightly Increase on Write speeds.

Example:

ALTER TABLE table_name ADD INDEX index_name (column);

Eg.: unique constraints, cascade deletes

Pros: Application agnostic Data Integrity

Cons: -

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:

general_log_file = /var/log/mysql_queries.log
general_log = 1

check log with:

tail -f /var/log/mysql_queries.log

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:

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;