Category: MySQL

MySQL LOAD DATA Trick:

I leaned a new trick today with LOAD DATA INFILE. I’m migrating some data from an external source, and the Date Format is not the MySQL required YYYY-MM-DD, it was DD-MMM-YY. So how do you load this into a Date Field.


$ echo "02-FEB-07" > /tmp/t1.psv
$ mysql -umysql
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(d1 DATE);
# echo "02-FEB-07" > /tmp/t1.psv
LOAD DATA LOCAL INFILE '/tmp/t1.psv'
INTO TABLE t1 (@var1)
SET d1=STR_TO_DATE(@var1,'%d-%M-%y');
SELECT * FROM t1;
EXIT


The trick is to bind the appropriate column within the file being loaded to a variable, @var1 in my example and use the SET syntax to perform a function on the variable. Rather cool.

A good tip to know.

(Via Planet MySQL.)

This is sweet!

How to find the max row per group in SQL without subqueries – Xaprb:

A while ago I wrote about how to select the first, minimum, maximum or least row per group in SQL. This article shows how to solve this problem without subqueries.

Like many SQL problems, the key to understanding the solution is to rephrase the English question to make it easy to translate into SQL.

Compiling MySQL from Source:

WebObjects > MySQL >
Compiling MySQL from Source

OK, I just upgraded to a MacBook Pro Core 2 Duo. Generally the binary distributions are easier to work with, but I need SSL connections capability, so I have to compile from source to have that feature included. Her is how………..

Objectives
– Same directory layout as the standard binary installation.
– Same general features as typical standard binary installation
– SSL client connection support

I performed my source installation with help from MySQL guide and this Apple Developer article with notes/clarifications for OS X Tiger.

Starting config. Mac OS X Tiger 10.4.8, XCode 2.4.1 (available on your Tiger install disk or from connect.apple.com free online membership)

OpenSSL is already installed as part of OS X
$ openssl version
OpenSSL 0.9.7l 28 Sep 2006

Turn off auto-expand in your browser and download the tarball source distribution from mysql.com. I downloaded version 4.1.22 and put it in a directory named ‘dev’ in my ho

MySQL Performance Blog » MySQL Session variables and Hints

MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example
SELECT STRAIGHT_JOIN * FROM A FORCE INDEX(A) JOIN B

Selecting random record from MySQL database table.

[PHP]

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );


O’Reilly Network — MySQL Federated Tables: The Missing Manual

One of the most exciting features introduced in MySQL 5 is the federated engine. The ability to access data from a remote server without the constraints of replication tickles every programmer’s fancy.

Most interesting. Pay very careful attention to the index rules!

Need Some Space?:

In this case, MySQL dutifully creates copies of the table’s .MYD and .MYI files in the new location, then replaces the originals with symlinks. Hey, presto! You’ve just moved all the bulky bits of the table. And if the path points to a location on a different disk, then you’ve just freed up a chunk of space on the original drive.

(Via Planet MySQL.)

How To Index For Joins

“What do I index to join these tables efficiently?”

mySQL join explained