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
May 122011
May 022011
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
Apr 272011
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.
Apr 072011
Drop a collection in MongoDB
Answer:
To drop a collection in MongoDB, enter the interactive shell, and type:
db.foo.drop()
If you want to remove all the objects only, not dropping the collection, try
db.foo.remove()
Apr 052011
Insert a new document to MongoDB
Answer:
To insert a new document to MongoDB, try the following statement in the shell,
# db.test.save({ "foo" : "bar"});
So you have saved a key-value pair in the test document.
