LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 07-20-2006, 10:18 PM   #1
GATTACA
Member
 
Registered: Feb 2002
Location: USA
Distribution: Fedora, CENTOS
Posts: 209

Rep: Reputation: 32
Make MySQL faster (Fedora Core 5 x86_64)


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.
 
Old 07-21-2006, 04:07 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: earth
Distribution: slackware by choice, others too :} ... android.
Posts: 23,067
Blog Entries: 11

Rep: Reputation: 917Reputation: 917Reputation: 917Reputation: 917Reputation: 917Reputation: 917Reputation: 917Reputation: 917
I can't see an actual query ...


Cheers,
Tink
 
Old 07-21-2006, 06:44 PM   #3
GATTACA
Member
 
Registered: Feb 2002
Location: USA
Distribution: Fedora, CENTOS
Posts: 209

Original Poster
Rep: Reputation: 32
Quote:
Originally Posted by Tinkster
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;
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Some problems/questions for Fedora Core 5 x86_64 Panagiotis_IOA Fedora 5 06-15-2006 08:37 PM
Fedora Core 4 x86_64 on a Shuttle XPC SN25P with an AMD 64 X2 (Dual core) gwiesenekker Linux - Hardware 4 12-06-2005 11:53 PM
Linuxant DriverLoader on Fedora Core X86_64 Clark Bent Linux - Laptop and Netbook 3 03-09-2005 09:24 AM
how do I make fedora load faster? sdat1333 Fedora 18 11-26-2004 05:53 PM
Fedora Core 3 x86_64 RPMs Chebyshev Fedora 0 11-09-2004 01:50 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 10:11 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration