FiveTech Software tech support forums

www.FiveTechSoft.com
https://fivetechsoft.com/forums/

Need SQL SELECT Statement advice.

https://fivetechsoft.com/forums/viewtopic.php?f=3&t=38969

Page 1 of 1

Need SQL SELECT Statement advice.

Posted: Fri May 15, 2020 9:09 pm
by Horizon
Hi,

I have an SQL SELECT statement below. It is slow for big tables.

Is there any advices for faster approach. ('alacak', 'borc' and 'mahkeme' has more records for one record of 'icrakart')

Code: Select all

SELECT m.IID, m.KAYIT_NO, m.T1_INFAZ, m.D_DAIRE, m.DOSNO, m.REF_NO1, m.REF_NO2, m.SONKOD,
(SELECT ALACAK FROM alacak AS c1 WHERE c1.AL_NO = m.IID LIMIT 1) AS ALACAK,
(SELECT BORCLU FROM borc AS c2 WHERE c2.BR_NO = m.IID LIMIT 1) AS BORCLU,
(SELECT MH_5 FROM mahkeme AS c3 WHERE c3.MH_NO = m.IID LIMIT 1) AS MH_5
FROM icrakart AS m
ORDER BY m.IID DESC
Thanks,

Re: Need SQL SELECT Statement advice.

Posted: Sat May 16, 2020 12:50 pm
by vilian
Have you already tried by this way ?

Code: Select all

SELECT m.IID, m.KAYIT_NO, m.T1_INFAZ, m.D_DAIRE, m.DOSNO, m.REF_NO1, m.REF_NO2, m.SONKOD, c1.ALACAK, c2.BORCLU, c3.MH_5 
FROM icrakart AS m 
LEFT JOIN alacak AS c1 ON m.IID = c1.AL_NO 
LEFT JOIN borc AS c2 ON m.IID = c2.BR_NO 
LEFT JOIN mahkeme AS c3 ON m.IID = c3.MH_NO
ORDER BY m.IID DESC

Re: Need SQL SELECT Statement advice.

Posted: Sat May 16, 2020 3:21 pm
by Horizon
vilian wrote:Have you already tried by this way ?

Code: Select all

SELECT m.IID, m.KAYIT_NO, m.T1_INFAZ, m.D_DAIRE, m.DOSNO, m.REF_NO1, m.REF_NO2, m.SONKOD, c1.ALACAK, c2.BORCLU, c3.MH_5 
FROM icrakart AS m 
LEFT JOIN alacak AS c1 ON m.IID = c1.AL_NO 
LEFT JOIN borc AS c2 ON m.IID = c2.BR_NO 
LEFT JOIN mahkeme AS c3 ON m.IID = c3.MH_NO
ORDER BY m.IID DESC
Yes, I have tried. Result is not same.

Your advice : (just one change. iid=46)

Code: Select all

SELECT m.IID, m.KAYIT_NO, m.T1_INFAZ, m.D_DAIRE, m.DOSNO, m.REF_NO1, m.REF_NO2, m.SONKOD, c1.ALACAK, c2.BORCLU, c3.MH_5
FROM icrakart AS m
LEFT JOIN alacak AS c1 ON m.IID = c1.AL_NO
LEFT JOIN borc AS c2 ON m.IID = c2.BR_NO
LEFT JOIN mahkeme AS c3 ON m.IID = c3.MH_NO
WHERE m.IID = 46
ORDER BY m.IID DESC
Result :

Code: Select all

IID KAYIT_NO    T1_INFAZ    D_DAIRE DOSNO   REF_NO1 REF_NO2 SONKOD  ALACAK  BORCLU  MH_5
46  55  0   5   2019/10707  11111   \N  0   AKYÜZ HOLDİNG BİRİNCİ BORÇLU  \N
46  55  0   5   2019/10707  11111   \N  0   AKYÜZ HOLDİNG İKİNCİ BORÇLU   \N
46  55  0   5   2019/10707  11111   \N  0   AKYÜZ HOLDİNG ÜÇÜNÇÜ BORÇLU \N
46  55  0   5   2019/10707  11111   \N  0   AKYÜZ HOLDİNG DÖRDÜNCÜ BORÇLU \N
46  55  0   5   2019/10707  11111   \N  0   AKYÜZ HOLDİNG Av. Birinci Avukat  \N
46  55  0   5   2019/10707  11111   \N  0   AKYÜZ HOLDİNG Av. İkinci Avukat  \N
46  55  0   5   2019/10707  11111   \N  0   AKYÜZ HOLDİNG BEŞİNCİ BORÇLU  \N
 
My SQL

Code: Select all

SELECT m.IID, m.KAYIT_NO, m.T1_INFAZ, m.D_DAIRE, m.DOSNO, m.REF_NO1, m.REF_NO2, m.SONKOD,
(SELECT ALACAK FROM alacak AS c1 WHERE c1.AL_NO = m.IID LIMIT 1) AS ALACAK,
(SELECT BORCLU FROM borc AS c2 WHERE c2.BR_NO = m.IID LIMIT 1) AS BORCLU,
(SELECT MH_5 FROM mahkeme AS c3 WHERE c3.MH_NO = m.IID LIMIT 1) AS MH_5
FROM icrakart AS m
WHERE m.IID = 46
ORDER BY m.IID DESC
Result :

Code: Select all

IID KAYIT_NO    T1_INFAZ    D_DAIRE DOSNO   REF_NO1 REF_NO2 SONKOD  ALACAK  BORCLU  MH_5
46  55  0   5   2019/10707  11111   \N  0   AKYÜZ HOLDİNG BİRİNCİ BORÇLU  \N
 
Thanks.

Re: Need SQL SELECT Statement advice.

Posted: Sat May 16, 2020 4:33 pm
by Marc Vanzegbroeck
Hi,

Do you use indexes?
I noticed that with indexes my queries are much faster then without.

Re: Need SQL SELECT Statement advice.

Posted: Sat May 16, 2020 6:48 pm
by vilian
Try this so:

Code: Select all

SELECT m.IID, m.KAYIT_NO, m.T1_INFAZ, m.D_DAIRE, m.DOSNO, m.REF_NO1, m.REF_NO2, m.SONKOD, c1.ALACAK, c2.BORCLU, c3.MH_5
FROM icrakart AS m
LEFT JOIN alacak AS c1 ON m.IID = c1.AL_NO
LEFT JOIN borc AS c2 ON m.IID = c2.BR_NO
LEFT JOIN mahkeme AS c3 ON m.IID = c3.MH_NO
WHERE m.IID = 46 GROUP BY m.IID 
ORDER BY m.IID DESC

Re: Need SQL SELECT Statement advice.

Posted: Sat May 16, 2020 7:58 pm
by Horizon
vilian wrote:Try this so:

Code: Select all

SELECT m.IID, m.KAYIT_NO, m.T1_INFAZ, m.D_DAIRE, m.DOSNO, m.REF_NO1, m.REF_NO2, m.SONKOD, c1.ALACAK, c2.BORCLU, c3.MH_5
FROM icrakart AS m
LEFT JOIN alacak AS c1 ON m.IID = c1.AL_NO
LEFT JOIN borc AS c2 ON m.IID = c2.BR_NO
LEFT JOIN mahkeme AS c3 ON m.IID = c3.MH_NO
WHERE m.IID = 46 GROUP BY m.IID 
ORDER BY m.IID DESC
Yes, it is what I wanted. I will check the the query time.

Thanks

Re: Need SQL SELECT Statement advice.

Posted: Sat May 16, 2020 8:00 pm
by Horizon
Marc Vanzegbroeck wrote:Hi,

Do you use indexes?
I noticed that with indexes my queries are much faster then without.
Hi Marc,

I am rookie for mysql select. I will try.

Thanks.

Re: Need SQL SELECT Statement advice.

Posted: Tue May 19, 2020 2:19 pm
by Horizon
Horizon wrote:
vilian wrote:Try this so:

Code: Select all

SELECT m.IID, m.KAYIT_NO, m.T1_INFAZ, m.D_DAIRE, m.DOSNO, m.REF_NO1, m.REF_NO2, m.SONKOD, c1.ALACAK, c2.BORCLU, c3.MH_5
FROM icrakart AS m
LEFT JOIN alacak AS c1 ON m.IID = c1.AL_NO
LEFT JOIN borc AS c2 ON m.IID = c2.BR_NO
LEFT JOIN mahkeme AS c3 ON m.IID = c3.MH_NO
WHERE m.IID = 46 GROUP BY m.IID 
ORDER BY m.IID DESC
Yes, it is what I wanted. I will check the the query time.

Thanks
Villian,
The query time is not different using this method other than mine's where method.

Marc,
I have created some index and speed is amazingly increased. I should look more about indexes to develop.

Thanks for both.

Re: Need SQL SELECT Statement advice.

Posted: Tue May 19, 2020 3:03 pm
by vilian
Horizon,

Do you have indexes in these tables ? OR are c1.AL_NO,c2.BR_NO, c3_MH_NO primary Keys ?

Re: Need SQL SELECT Statement advice.

Posted: Tue May 19, 2020 3:07 pm
by Horizon
vilian wrote:Horizon,

Do you have indexes in these tables ? OR are c1.AL_NO,c2.BR_NO, c3_MH_NO primary Keys ?
Hi Villian,

These are not primary index. I have created another index for These vars.

alacak table.

Code: Select all

CREATE TABLE `alacak` (
    `AID` INT(11) NOT NULL AUTO_INCREMENT,
    `AL_NO` INT(11) NULL DEFAULT NULL,
    `ALACAK` VARCHAR(150) NULL DEFAULT NULL COLLATE 'latin5_turkish_ci',
    `ADRES1` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin5_turkish_ci',
    `MUV_NO` INT(11) NULL DEFAULT NULL,
    `VK_ID` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`AID`) USING BTREE,
    INDEX `AL_NO_idx` (`AL_NO`) USING BTREE
)
COLLATE='latin5_turkish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=55
;
 

Re: Need SQL SELECT Statement advice.

Posted: Tue May 19, 2020 5:48 pm
by vilian
I don't know your tables, but IF you create indexes for the tables, your select will improve a lot.

icrakart create a index for IID
alacak create a index for AL_NO
borc create a index for BR_NO
mahkeme create a index for MH_NO
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited