topic-38969

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Need SQL SELECT Statement advice.
Posted: Fri May 15, 2020 09:09 PM

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 (fw): Select all Collapse

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,

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

vilian

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Re: Need SQL SELECT Statement advice.
Posted: Sat May 16, 2020 12:50 PM

Have you already tried by this way ?

Code (fw): Select all Collapse

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

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: Need SQL SELECT Statement advice.
Posted: Sat May 16, 2020 03:21 PM

vilian wrote:Have you already tried by this way ?

Code (fw): Select all Collapse

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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.

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Need SQL SELECT Statement advice.
Posted: Sat May 16, 2020 04:33 PM

Hi,

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

vilian

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Re: Need SQL SELECT Statement advice.
Posted: Sat May 16, 2020 06:48 PM

Try this so:

Code (fw): Select all Collapse

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

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: Need SQL SELECT Statement advice.
Posted: Sat May 16, 2020 07:58 PM

vilian wrote:Try this so:

Code (fw): Select all Collapse

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

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: Need SQL SELECT Statement advice.
Posted: Sat May 16, 2020 08:00 PM

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.

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: Need SQL SELECT Statement advice.
Posted: Tue May 19, 2020 02:19 PM

Horizon wrote:

vilian wrote:Try this so:

Code (fw): Select all Collapse

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.

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

vilian

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Re: Need SQL SELECT Statement advice.
Posted: Tue May 19, 2020 03:03 PM

Horizon,

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

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: Need SQL SELECT Statement advice.
Posted: Tue May 19, 2020 03:07 PM

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 (fw): Select all Collapse

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
;

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

vilian

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Re: Need SQL SELECT Statement advice.
Posted: Tue May 19, 2020 05:48 PM

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

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

FiveTech Support Forums

Continue the discussion