Bottomless, Endless

If the tables have the same schema, you can directly do a subquery with a SELECT *:

INSERT INTO pirates_myspace.users SELECT * FROM mafiawars_myspace.users LIMIT 500;

However if there is an auto-increment field in the schema, you will have to select NULL for the auto-increment column…:

INSERT INTO pirates_myspace.items (SELECT NULL, now(), modified, expiration, type, category, name FROM mafiawars_myspace.items WHERE id >=150);

… meaning you will need to type one by one all the column names you want to copy over, which in most of the cases mean all of them minus the auto-increment field.
Yikes.

If you want to join two tables but one has only partial information, you cannot use the implicit join, you need a LEFT OUTER JOIN.

Here’s a concrete example using two tables, Users and Groups:

Users:
ID NAME
1 Pierre
55 David
66 Roger

Groups:
LEADER_ID MEMBER_ID
1 55
1 66
1 -1

If you want to list all the members of the group belonging to Pierre, you expect to be returned IDs 55, 66 and -1.

Doing it the implicit way will not return -1, as he’s not listed in the Users table:

SELECT member_id, name FROM groups, users WHERE member_id=id AND leader_id='1';
55 DAVID
66 ROGER

Using the LEFT OUTER JOIN will return this ghost user:

SELECT member_id, name FROM groups LEFT OUTER JOIN users ON groups.member_id = users.id WHERE leader_id='1';
55 DAVID
66 ROGER
-1 NULL

Here is a good explanation on LEFT OUTER JOIN.
(They use these tables in their example)

The idea is to reduce the number of locks, hence the contention.

Use dirty reads: the table doesn’t get locked for reads, but still locks for inserts.

This is ideal if you don’t need absolutely up to date data but still need to record accurately the changes.
An example would be for statistics retrieval on a website: you need to continue recording accurately the actions of your users, but you don’t need accurate statistics.

To enable dirty reads on MySQL:

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

bash# mysqldump mafiawars_myspace -u root -p > db.sql

Then log in MySQL:

bash# mysql -u root -p
mysql> create database mafiawars_myspace_test
mysql> use mafiawars_myspace_test
mysql> source db.sql

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute.

See manual

If you try to print another column than just a count(*) without specifying a GROUP BY clause, you will get this error:

mysql> SELECT description, count(*) FROM my_table WHERE action='post' AND result='fail';
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql>

Just add the name of the extra column you are trying to print in the GROUP BY clause and you’re done:

mysql> SELECT description, count(*) FROM my_table WHERE action='post' AND result='fail' GROUP BY 'description';

This has a practical application in printing a Top 10 of most successful players:

mysql> SELECT name, count(*) FROM my_table WHERE action='fight' AND result='success' GROUP BY 'name' LIMIT 10;

InnoDB is not always the best choice.

MyISAM locks the whole table to update only one row, so it’s hard when app requires lots of W(or RW): in this case InnoDB is better.

Bottom line:

if the table is mainly R: MyISAM better because faster

if the table is mainly W: InnoDB better because faster