Posts Tagged ‘MySql’

MySql and the Art of Sql

Posted on July 2nd, 2009 • Filed under MySql • No Comments

I’ve been reading the The Art of SQL and it makes some very sensible suggestions regarding query performance.

Here’s somewhere I’ve implemented 1 of the techniques.

The task: return records for this month.

Usually I’d write something like

select * from results where month(dateadded) = month(now()) and year(dateadded) = year(now())

The problem with this is that 2 functions (month() and year()) need to be applied to every row to filter the results, this obviously requires overhead.  Also we’re missing out on any indexes that might be on the dateadded field.

A better approach is to use

select * from results where dateadded >= DATE_FORMAT(now() ,'%Y-%m-01')

With this approach 1 function is run to find the first day of the month, and if the dateadded field is indexed we’ll benefit from this.

 

Import csv data into MySql

Posted on May 8th, 2009 • Filed under MySql • No Comments

Unusually for an asp.net developer, MySql is my database platform of choice – very quick and less server intensive that certain other database platforms I could mention…

Anyway here’s how I go about loading csv data into MySql

• Prepare your data in a program such as excel or open office
• Save as comma delimied or tab delimited in the file system where mysql stores the data, e.g. if the data is to go in a database called “test” then save the csv in C:\Program Files\MySQL\MySQL Server 5.0\data\test
• Create a temporary import table whose structure matches the file to import
• Make each field a varchar(255) even if the field contains numbers, we can always remove rogue data later – let’s just get it into the database first!
• Load in the data (note: this is ignoring the first line as it contains the column headers and is set for a tab delimited file)

LOAD DATA INFILE 'myimport.txt' INTO TABLE test.tempimporttable

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES