Need SQL SELECT Statement advice.

[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  Expand view

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,

[vilian]

Have you already tried by this way ?

Code: Select all  Expand view

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

[Horizon]

Have you already tried by this way ?

Code: Select all  Expand view

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  Expand view

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  Expand view

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  Expand view

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  Expand view

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.

[Marc Vanzegbroeck]

Hi,

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

[vilian]

Try this so:

Code: Select all  Expand view

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

[Horizon]

Try this so:

Code: Select all  Expand view

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

[Horizon]

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.

[Horizon]

Try this so:

Code: Select all  Expand view

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.

[vilian]

Horizon,

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

[Horizon]

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  Expand view

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
;
 

[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