Dump the table schema of a MySQL database
Answer:
To dump the schema of a MySQL database (hence, no data), you need just one command:
E.g.
# mysqldump my_db my_table --no-data
Order by predefined order in MySQL
Answer:
Assume you have a SQL statement like:
SELECT id FROM user WHERE id IN (5, 10, 6);
How to make sure the returned rows are in the exact order as 5, 10, 6?
You can do the following:
SELECT id FROM user WHERE id IN (5, 10, 6) ORDER BY FIELD (id, 5, 10, 6);
Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
Explain query execution plan in MySQL
Answer:
To get a better understanding of the performance of your MySQL query, you can use the explain statement.
E.g.
mysql> explain select * from user where host = 'localhost';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | PRIMARY | PRIMARY | 180 | const | 12 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+Usually you need to look at the column "key" to see if index was being used, and the column "rows" to see how many rows are scanned for the query.
Mar 062011
List all the database size in MySQL
Answer:
The following command will list out all the databases size in MySQL
mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema Output:
+--------------------+----------------------+
| Data Base Name | Data Base Size in MB |
+--------------------+----------------------+
| test | 152.59375000 |
| information_schema | 0.00781250 |
| mysql | 0.61196423 |
+--------------------+----------------------+
Force MySQL to use a specific index during query processing
Answer:
Sometimes, the index picked by MySQL might not be the most optimized one, and you want to pick the index to use by yourself.
To do so, you can provide the index hints by doing something like the following.
mysql> SELECT * FROM user FORCE INDEX user_name WHERE name = 'John' AND user_name = 'john2';
So you tell MySQL to use the index user_name, instead of name (if also available).
That's all.
