Archive for August, 2008

Load data from a plain CSV text file

Wednesday, August 20th, 2008

Use the LOAD DATA query:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

To store a query to a CSV file:

mysql> select * from my_table INTO OUTFILE '/tmp/my_table' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Note that mysql cannot write anywhere in the file system, just in /tmp/ for security reasons, so just move the file afterwards.

Pushing MySQL updates to Memcache using SQL triggers

Tuesday, August 19th, 2008

How to make MySQL trigger cache updates after an INSERT or UPDATE on the DB.

CREATE TRIGGER tr1 AFTER INSERT ON table1 FOR EACH ROW DO memcache_set("localhost:11211",NEW.name,NEW.value);
CREATE TRIGGER tr1 AFTER DELETE ON table1 FOR EACH ROW DO memcache_delete("localhost:11211",OLD.name);

Source

Randomize an array in Javascript

Monday, August 4th, 2008
function fisherYates ( myArray ) {
  var i = myArray.length;
  if ( i == 0 ) return false;
  while ( --i ) {
     var j = Math.floor( Math.random() * ( i + 1 ) );
     var tempi = myArray[i];
     var tempj = myArray[j];
     myArray[i] = tempj;
     myArray[j] = tempi;
   }
}

Source

Add a key to speed up queries

Friday, August 1st, 2008
ALTER TABLE items ADD KEY amount_lookup (amount);