Hello,

This question is for the database guru's out there.

I've got a massive (14GB) flat file that contains redundancies. Let me explain the table structure:
id (INT): primary key
name (VARCHAR(50)): text field 1
description (VARCHAR(505)): text field 2.

Obviously the 'id' is unique in the table, however the other 2 fields are not.

I want to pickout just the first instance of a record from this table. For instance if I have 3 records like this:
1 name_data This is a text field
2 name_data This is a text field
3 name_data This is a text field

I just want the first row since the other two records are identical except for their 'id' values.


I've managed to get the query as optimized as possible, however I'm sure I can make MySQL go faster.

Here are the hardware specs:
Athlon64 X2 (1MB L2 cache per core)
RAID 0 with two 7200rpm disks
4GB physical RAM
OS: Fedora Core 5

I've read that using the MyISAM table engine is better for speed in MySQL so I've made sure all my tables are in this format.

This is a one time job (hence the use of RAID 0). I'm not worried about losing my data since I can always reload the table easily/quickly. Having said that, being able to roll-back commands is unnecessary.

Can anyone suggest other options/parameters can I use in the /etc/my.cnf file (or when starting up the mysql server) to make this query finish ASAP?

Thanks.

I can't see an actual query ...


Cheers,
Tink

Well my qestion really has more to do with the MySQL '/etc/my.cnf' file and/or MySQL server parameters to make it go faster. I've already got the query:

--get the frequency of each name field and store it in a table
create table nameCount engine = myisam
select name, count(*) as freq
from redundantTable
group by name;

go

--now get the id's for first instance of a 'name' value
create table oneId engine = myisam
select (
select r.id
from redundantTable r
where r.id = n.id
limit 1
) as id
from nameCount n
where n.freq > 1;

go

--final (result) table, gives only one instance of 'name' and 'description'
create table final engine = myisam
select o.id, r.name, r.description
from oneId o, redundantTable r
where o.id = r.id;