Paul: /* Database dumps */

2021-11-08T10:56:21Z

Database dumps

← Older revision		Revision as of 10:56, 8 November 2021
Line 101:		Line 101:
	== Database dumps ==		== Database dumps ==

	Fastest way to create a database dump is to pass <code>--quick</code> and <code>--single-transaction</code> to <code>mysqldump</code>. This will only work if all your tables are InnoDB (which they should be, unless you have very specific use cases).		Fastest way to create a database dump is to pass <code>--quick</code> and <code>--single-transaction</code> to <code>mysqldump</code>. This will only work if all your tables are InnoDB (which they should be, unless you have very specific use cases). In theory you should also pass <code>--opt</code>, but in practice this is enabled by default.

	[[Category:MySQL]]		[[Category:MySQL]]

Paul at 10:55, 8 November 2021

2021-11-08T10:55:53Z

← Older revision		Revision as of 10:55, 8 November 2021
Line 98:		Line 98:

	ALTER TABLE my_table ADD KEY (my_column)		ALTER TABLE my_table ADD KEY (my_column)

			== Database dumps ==

			Fastest way to create a database dump is to pass <code>--quick</code> and <code>--single-transaction</code> to <code>mysqldump</code>. This will only work if all your tables are InnoDB (which they should be, unless you have very specific use cases).

	[[Category:MySQL]]		[[Category:MySQL]]

Paul at 14:59, 4 March 2018

2018-03-04T14:59:44Z

← Older revision		Revision as of 14:59, 4 March 2018
Line 99:		Line 99:
	ALTER TABLE my_table ADD KEY (my_column)		ALTER TABLE my_table ADD KEY (my_column)

	[[Category::MySQL]]		[[Category:MySQL]]

Paul at 14:59, 4 March 2018

2018-03-04T14:59:22Z

← Older revision		Revision as of 14:59, 4 March 2018
Line 98:		Line 98:

	ALTER TABLE my_table ADD KEY (my_column)		ALTER TABLE my_table ADD KEY (my_column)

			[[Category::MySQL]]

Paul: Created page with "== Connections == The MySQL protocol is half-duplex, which means that it can send or receive messages, but not both at the same time. == Query cache == Looking up an item i..."

2018-03-04T14:58:09Z

Created page with "== Connections == The MySQL protocol is half-duplex, which means that it can send or receive messages, but not both at the same time. == Query cache == Looking up an item i..."

New page

== Connections ==

The MySQL protocol is half-duplex, which means that it can send or receive
messages, but not both at the same time.

== Query cache ==

Looking up an item in the query cache is a hash operation.

== Profiling queries ==

Enable the profiler for this session (note this method is deprecated in later versions of Percona):

SET PROFILING = 1

Run query:

SELECT * FROM table

Show the profiles (summary of query and time taken):

SHOW PROFILES

Show detailed information for a specific query:

SHOW PROFILE FOR QUERY 1

To clear out the query cache:

RESET QUERY CACHE

== Using EXPLAIN ==

A simple test to see if indexes are being used:

EXPLAIN SELECT * FROM table WHERE column = ?

If the <code>rows</code> value returned is significantly higher than the number of rows fetched by the query and the <code>type</code> value is <code>ALL</code> then it is likely that MySQL is performing a full table scan. Adding an index can significantly reduce the number of rows scanned (though not always down to 1:1 scanned:fetched).

If the <code>extra</code> value is <code>Using where</code> then this also suggests that MySQL is discarding rows after scanning them, which may have performance implications.

== Locking ==

Locking has a trade-off effect on performance. On the one hand, a high-level lock (e.g. table lock) is cheap to obtain, but prevents reads and writes. A row-level lock is more expensive to obtain, but allows reads and writes to other
rows.

In some circumstances, the locking strategy is fixed. For example, <code>ALTER TABLE</code> commands will always require a table lock.

== Transactions ==

Transactions help to ensure that a collection of statements are executed as one atomic commit. There is a cost to this, and it may not be required for all applications.

== Storage engines ==

To find the storage engine for a given table:

SELECT engine FROM information_schema.tables
WHERE
table_schema = 'database'
AND table_name = 'table'

Storage engines for all tables in a given database:

SELECT table_name, engine FROM information_schema.tables
WHERE
table_schema = 'database'

=== MyISAM ===

MyISAM supports compressed tables using <code>myisampack</code>. Compressed tables use up less disk space and therefore require fewer seeks to load. They may be a good choice for read-only data, e.g. lookup tables.

The major downside to MyISAM is that it uses table locking, and therefore it is not suitable for data which is regularly updated (e.g. sessions, logs).

=== Converting between storage engines ===

The simplest and easiest way to convert a table to another storage engine is with the <code>ALTER TABLE</code> command:

ALTER TABLE mytable ENGINE = 'InnoDB';

However, this performs a copy of each row in the table, and therefore is unlikely to be suitable for live data.

== Data types ==

Some broad advice:

* Simpler data types are easier to process.
* Integers are cheaper to compare than strings.
* Use integers for IP addresses.
* Avoid NULL where possible as it is harder for MySQL to optimise queries which may involve NULL values.

== Joins ==

MySQL has a limit of 61 tables per join.

== Indexes ==

Adding a b-tree index to an InnoDB table:

ALTER TABLE my_table ADD KEY (my_column)

MySQL Performance - Revision history

Paul: /* Database dumps */

Paul at 10:55, 8 November 2021

Paul at 14:59, 4 March 2018

Paul at 14:59, 4 March 2018

Paul: Created page with "== Connections == The MySQL protocol is half-duplex, which means that it can send or receive messages, but not both at the same time. == Query cache == Looking up an item i..."