First read the introduction on Opensocial
Then read about the sample Hello World application
Then read about developping an app
Is it faster to issue two SQL statements (DELETE then INSERT) or craft a single query using ON DUPLICATE KEY UPDATE?
INSERT INTO items SET user_id=3463574, item_id=1, amount = 1 ON DUPLICATE KEY UPDATE amount = amount + 1;
DELETE FROM items WHERE user_id=3463574 AND item_id=1; INSERT INTO items SET user_id=3463574 , item_id=1, amount = 66;
Difference in time (using microtime() in PHP):
ON DUP – 0.00023600000000001
ON DUP – 0.00022699999999998
ON DUP – 0.00014599999999998
ON DUP – 0.00014899999999998
DEL AND INSERT – 0.000336
ON DUP – 0.00015000000000004
DEL AND INSERT – 0.00046600000000002
The del/insert is 3 times slower than the ON DUPLICATE statement.
You have a table to migrate from MyISAM to InnoDB, and while you’re at it, you want to modify the definition of some columns.
For instance we want to change the engine of the following table, along with changing the “amount” column to an unsigned int:
mysql> show create table items; +-------+--------------------------------------+ | Table | Create Table | +-------+--------------------------------------+ | items | CREATE TABLE `items` ( `user_id` bigint(20) NOT NULL, `item_id` tinyint(4) NOT NULL, `amount` int(11) default '0', `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`,`item_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
You could do that in two queries…:
mysql> ALTER TABLE items MODIFY COLUMN amount INT UNSIGNED; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE items ENGINE InnoDB; Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0
… but in practice it will take twice as long as a single query, as every time MySQL will have to make a copy of the whole table anyway.
So here’s the single query:
mysql> ALTER TABLE items MODIFY COLUMN amount INT UNSIGNED, ENGINE InnoDB;
This operation is extremely slow so keep that in mind on your production server.
For instance it took 6 minutes to execute on a staging server with only 700k rows.
The production DB has 8 Million rows…
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.
You changed your files locally, all is neat and clean and you are ready to commit.
But you know others may have worked on the same portions of code you worked on, so you fear that doing “svn up” before committing will pollute your neat local files with conflict resolution code sections.
You need at least a heads up, here’s how:
svn st -u
It will diff your local files with the current head on the server.
Here’s how to tell which files were changed between two directories, if you don’t need to know the details of the differences:
diff -Bbrq folder1 folder2
-B = Ignore changes that just insert or delete blank lines.
-b = Ignore changes in amount of white space.
-r = When comparing directories, recursively compare any subdirectories found.
-q = Report only whether the files differ, not the details of the differences.
It comes out handy to compare two directories checked out of SVN (for instance the prod folder and the staging folder).
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:
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
New lines at the end of PHP files cause problems when including the file as you may not want this extra line.
For instance it messes up redirections in Myspace applications.
Basically it means you cannot retrieve one column at a time, as the first column you retrieve will move the pointer to the end of the data, and as you cannot rewind, the next call to retrieve the second column will return nothing, because you’re already at the end of the data.
$sql = "SELECT response, response_comment FROM responses"; $stmt = $dbHandle->prepare($sql); $stmt->execute(); $stats = $stmt->fetchall(pdo::FETCH_COLUMN, 0);
Then if you proceed to fetch the second column… :
$stats = $stmt->fetchall(pdo::FETCH_COLUMN, 1);
… it will return nothing.
You would have to execute the query again before running a second fetchall().
If it had worked, it could have been used to get the 2 columns in 2 arrays, then run an array_combine() to get the columns of the first array as keys and the columns of the second array as values. Too bad.
© 2007-2013 Bottomless, Inc. All Rights Reserved
Switch to our mobile site