01-25-10, 14:37 #1Registered User
- Join Date
- Jan 2010
Unanswered: Advanced algorithm of an existing MySQL query
I have a table consists of
CREATE TABLE /*!32312 IF NOT EXISTS*/ `bilgiler` ( `kayitID` int(11) NOT NULL AUTO_INCREMENT, `hak_sahibi_id` bigint(15) DEFAULT NULL, `cinsiyet` tinyint(1) DEFAULT NULL, `dogumtarihi` date DEFAULT NULL, `islemtarihi` date DEFAULT NULL, `tanikodu` varchar(10) DEFAULT NULL, `tedavi_ili` tinyint(3) DEFAULT NULL, `dogum_ili` tinyint(3) DEFAULT NULL, `ikamet_ili` tinyint(3) DEFAULT NULL, PRIMARY KEY (`kayitID`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin5;
Select bilgiler.hak_sahibi_id,bilgiler.cinsiyet,date_format(bilgiler.dogumtarihi,'%d.%m.%Y') as dtarihi,date_format(bilgiler.islemtarihi, '%d.%m.%Y') as itarihi,(To_days( bilgiler.islemtarihi ) - TO_DAYS( bilgiler.dogumtarihi )) as difference,bilgiler.tanikodu,bilgiler.dogum_ili,bilgiler.ikamet_ili,bilgiler.tedavi_ili,b.repeat from bilgiler INNER JOIN ( SELECT hak_sahibi_id,year(islemtarihi) AS yyyy,COUNT(*) AS 'repeat' from bilgiler group by hak_sahibi_id,year(islemtarihi) ) as b on b.hak_sahibi_id = bilgiler.hak_sahibi_id and b.yyyy = year(bilgiler.islemtarihi) into outfile 'C:/outcome/results.txt' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
It gives the results perfectly as I want. My problem is the file size is too big. There are 10 million records on this table and code runs very slow. It takes nearly a month with the query.
Is it possible to write a better code makes the same job faster? Is it possible to make changes on MySQL settings to make this query run faster (currently it is using the default settings of MySQL)?
01-25-10, 16:13 #2SQL Consultant
- Join Date
- Apr 2002
- Toronto, Canada
01-25-10, 16:52 #3Registered User
Provided Answers: 11
- Join Date
- Jan 2003
I would start by making sure that MySQL has access to enough memory to run through 10 million records. Ideally a machine with 4GB of memory if it is a 32 bit operating system. This is something I would not leave to a laptop, or a desktop machine. No idea where MySQL hides its memory configuration, though.
What sort of machine is this running on?
01-25-10, 18:21 #4vaguely human
Originally Posted by telmessos
- Join Date
- Jun 2007
Not formatting the code makes it difficult to read but using Turkish/Greek (?) field names and table names is really asking a bit much from us geeks but I'd start by taking the data from :
SELECT hak_sahibi_id,year(islemtarihi) AS yyyy, COUNT(*) AS 'repeat' from bilgiler group by hak_sahibi_id, year(islemtarihi)
update bilgiler set yyyy = year( islemtarihi )
Select b.hak_sahibi_id, b.cinsiyet, date_format(b.dogumtarihi,'%d.%m.%Y') as tarihi, date_format(b.islemtarihi, '%d.%m.%Y') as itarihi, (To_days( b.islemtarihi ) - TO_DAYS( b.dogumtarihi )) as difference, b.tanikodu, b.dogum_ili, b.ikamet_ili, b.tedavi_ili, b.repeat from bilgiler b , fhgjek c where b.hak_sahibi_id = c.hak_sahibi_id and b.yyyy = c.yyyy into outfile 'C:/outcome/results.txt' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
Originally Posted by telmessosIs it the output file that's too big? If so then look up the limit command in MySQL - this should allow you to split the output into multiple files ie something like:Mike
... limit 1,2500000 ... limit 2500001,5000000 ... limit 5000001,7500000 ... limit 7500001,20000000
Are you sure yyyy is not a reserved word?
I'm not sure about 'repeat' either
I obviously haven't tested anything here