[ ID ]<->[ ID ]<->H |-->[ gsc_XX(many)] |->[ gba_XX(many)] [ NDVI_99_24 ] E | [ Others... ] | [ Others... ] [ NDVI_99_25 ] R | [=============] | [=============] [ Others... ] S | | [============] | | | | | [=============] | [=============] | [T: refmgsc ] | [T: refmgba ] | [=============] | [=============] |--> [ ID(1) ] |--> [ ID(1) ] [ BURNT ] [ BURNT ] [ Others... ] [ Others... ] [=============] [=============] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <1142595402.039841.93100@i39g2000cwa.googlegroups.com> [ref] => [htmlstate] => on_nl2br [postusername] => stefaan.lhermitte@agr.kuleuven.ac.be [ip] => stefaan.lhermit [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) -->[ ID ]<->[ ID ]<->H > |-->[ gsc_XX(many)] |->[ gba_XX(many)] [ NDVI_99_24 ] E > | [ Others... ] | [ Others... ] [ NDVI_99_25 ] R > | [=============] | [=============] [ Others... ] S > | | [============] > | | > | | > | [=============] | [=============] > | [T: refmgsc ] | [T: refmgba ] > | [=============] | [=============] > |--> [ ID(1) ] |--> [ ID(1) ] > [ BURNT ] [ BURNT ] > [ Others... ] [ Others... ] > [=============] [=============] >[/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <4a115$44224805$57d40752$14319@news.versatel.net> [ref] => <1142595402.039841.93100@i39g2000cwa.googlegroups.com> [htmlstate] => on_nl2br [postusername] => Dikkie Dik [ip] => nospam@nospam.o [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Optimize JOIN to gain speed - MySQL
Professional Web Applications Themes

Optimize JOIN to gain speed - MySQL

Dear mysql-ians, I am performing a query that takes ages before showing a result. I suppose it is very time consuming because of the JOIN I perform. My question therefore is if you have any suggestions to optimize my query? What I want to do is divide a value of a cell (NDVI) by the median of its neighbours based on geographical coordinates (refgeo). The selection script is as follows: SELECT NDVI.id, NDVI.NDVI_99_24*IF(B.B_99_24<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_24*IF(refB.B_99_24<30,1,NUL L)), NDVI.NDVI_99_25*IF(B.B_99_25<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_25*IF(refB.B_99_25<30,1,NUL L)), NDVI.NDVI_99_26*IF(B.B_99_26<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_26*IF(refB.B_99_26<30,1,NUL L)), ...., NDVI.NDVI_99_170*IF(B.B_99_170<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_170*IF(refB.B_99_170<30,1,N ULL)) FROM vgt.NDVI AS NDVI INNER JOIN vgt.B AS B USING (id) INNER JOIN vgt.v_ecoclim AS eco USING (id) INNER ...

  1. #1

    Default Optimize JOIN to gain speed

    Dear mysql-ians,

    I am performing a query that takes ages before showing a result. I
    suppose it is very time consuming because of the JOIN I perform. My
    question therefore is if you have any suggestions to optimize my query?

    What I want to do is divide a value of a cell (NDVI) by the median of
    its neighbours based on geographical coordinates (refgeo).
    The selection script is as follows:

    SELECT
    NDVI.id,
    NDVI.NDVI_99_24*IF(B.B_99_24<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_24*IF(refB.B_99_24<30,1,NUL L)),
    NDVI.NDVI_99_25*IF(B.B_99_25<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_25*IF(refB.B_99_25<30,1,NUL L)),
    NDVI.NDVI_99_26*IF(B.B_99_26<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_26*IF(refB.B_99_26<30,1,NUL L)),
    ....,
    NDVI.NDVI_99_170*IF(B.B_99_170<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_170*IF(refB.B_99_170<30,1,N ULL))

    FROM vgt.NDVI AS NDVI
    INNER JOIN vgt.B AS B
    USING (id)
    INNER JOIN vgt.v_ecoclim AS eco
    USING (id)
    INNER JOIN vgt.v_vcf2001 AS vcf
    USING (id)
    ,
    (vgt.meta_gsc AS refmgsc
    INNER JOIN vgt.gsc AS refgsc
    ON refgsc.gsc_XX = refmgsc.id)
    INNER JOIN vgt.geo AS refgeo
    ON refgeo.id = refgsc.id
    INNER JOIN vgt.B AS refB
    ON refgeo.id = refB.id
    INNER JOIN vgt.v_ecoclim AS refeco
    ON refgeo.id = refeco.id
    INNER JOIN vgt.v_vcf2001 AS refvcf
    ON refgeo.id = refvcf.id
    INNER JOIN (vgt.gba AS refgba
    INNER JOIN vgt.meta_gba AS refmgba
    ON refgba.gba_XX = refmgba.id)
    ON refgeo.id=refgba.id
    INNER JOIN vgt.NDVI AS refNDVI
    ON refgeo.id = refNDVI.id
    WHERE NDVI.id= 1133568
    AND refgeo.X_coord > 1288
    AND refgeo.X_coord < 1328
    AND refgeo.Y_coord > 659
    AND refgeo.Y_coord < 699
    AND refmgba.burnt=0
    AND refmgsc.burnt=0
    AND refeco.v_landcov=eco.v_landcov
    AND refeco.v_lowreb=eco.v_lowreb
    AND refvcf.vcf_bare < vcf.vcf_bare+20
    AND refvcf.vcf_bare > vcf.vcf_bare-20
    AND refvcf.vcf_tree < vcf.vcf_tree+20
    AND refvcf.vcf_tree > vcf.vcf_tree-20
    AND refvcf.vcf_herb < vcf.vcf_herb+20
    AND refvcf.vcf_herb > vcf.vcf_herb-20
    GROUP BY refmgba.burnt

    I have thus several main-tables that have identical rows (and are
    linked by id in a 1to1-relatioship ) and I have 2 sub-tables in a
    manyto1-relationship. I have put it in a scheme below (view using
    Courier font for correct spacing).

    The main tables are: NDVI, B, eco, vcf, refgsc, refgeo, refeco, refgba,
    refB, refvcf, refNDVI.
    The 2 subtables: refmgba, refmgsc.

    Is my query correct? Do you have any solutions to make my query more
    efficient?
    I've studied the mysql-manual, but I don't see answers on the moment

    Thanks in advance,
    Stef

    Schematic overview tables:

    [=============] [=============] [============]
    [T_Main: gsc ] [T_Main: gba ] [T_Main:NDVI ] O
    [=============] [=============] [============] T
    [ ID ]<--->[ ID ]<->[ ID ]<->H
    |-->[ gsc_XX(many)] |->[ gba_XX(many)] [ NDVI_99_24 ] E
    | [ Others... ] | [ Others... ] [ NDVI_99_25 ] R
    | [=============] | [=============] [ Others... ] S
    | | [============]
    | |
    | |
    | [=============] | [=============]
    | [T: refmgsc ] | [T: refmgba ]
    | [=============] | [=============]
    |--> [ ID(1) ] |--> [ ID(1) ]
    [ BURNT ] [ BURNT ]
    [ Others... ] [ Others... ]
    [=============] [=============]

    stefaan.lhermitte@agr.kuleuven.ac.be Guest

  2. #2

    Default Re: Optimize JOIN to gain speed

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] schrieb:
    > Dear mysql-ians,
    Hi
    >
    >...
    > FROM vgt.NDVI AS NDVI
    > INNER JOIN vgt.B AS B
    > USING (id)
    > INNER JOIN vgt.v_ecoclim AS eco
    > USING (id)
    > INNER JOIN vgt.v_vcf2001 AS vcf
    > USING (id)
    > ,
    > (vgt.meta_gsc AS refmgsc
    Whats this here? Seems to be a cross join. How do you combine the 3
    combined tables with the ones below?
    > INNER JOIN vgt.gsc AS refgsc
    > ON refgsc.gsc_XX = refmgsc.id)
    > INNER JOIN vgt.geo AS refgeo
    > ON refgeo.id = refgsc.id
    > INNER JOIN vgt.B AS refB
    > ON refgeo.id = refB.id
    > INNER JOIN vgt.v_ecoclim AS refeco
    > ON refgeo.id = refeco.id
    > INNER JOIN vgt.v_vcf2001 AS refvcf
    > ON refgeo.id = refvcf.id
    > INNER JOIN (vgt.gba AS refgba
    > INNER JOIN vgt.meta_gba AS refmgba
    > ON refgba.gba_XX = refmgba.id)
    >...

    What indices are set?

    Regards
    Stefan
    >...
    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.4.2.1 (MingW32)

    iD8DBQFEIgPxyeCLzp/JKjARAvOnAKDHXpMZhPuSBy574mu8S161EIOKTACgwsYh
    PgSa+xHKWPq1aN7K7EVNmVA=
    =IX8L
    -----END PGP SIGNATURE-----
    Stefan Rybacki Guest

  3. #3

    Default Re: Optimize JOIN to gain speed

    > I am performing a query that takes ages before showing a result.

    Probably. It takes ages for me to understand what it should be doing.
    > I
    > suppose it is very time consuming because of the JOIN I perform. My
    > question therefore is if you have any suggestions to optimize my query?
    Split it up in comprehendable parts? Or, if your version of MySQL
    supports it, by organizing the comprehendable parts into stored
    procedures. From there, you can start optimizing.

    You might also take a good look at what the EXPLAIN command has to say
    about this query.

    Best regards
    >
    > What I want to do is divide a value of a cell (NDVI) by the median of
    > its neighbours based on geographical coordinates (refgeo).
    > The selection script is as follows:
    >
    > SELECT
    > NDVI.id,
    > NDVI.NDVI_99_24*IF(B.B_99_24<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_24*IF(refB.B_99_24<30,1,NUL L)),
    > NDVI.NDVI_99_25*IF(B.B_99_25<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_25*IF(refB.B_99_25<30,1,NUL L)),
    > NDVI.NDVI_99_26*IF(B.B_99_26<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_26*IF(refB.B_99_26<30,1,NUL L)),
    > ...,
    > NDVI.NDVI_99_170*IF(B.B_99_170<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_170*IF(refB.B_99_170<30,1,N ULL))
    >
    > FROM vgt.NDVI AS NDVI
    > INNER JOIN vgt.B AS B
    > USING (id)
    > INNER JOIN vgt.v_ecoclim AS eco
    > USING (id)
    > INNER JOIN vgt.v_vcf2001 AS vcf
    > USING (id)
    > ,
    > (vgt.meta_gsc AS refmgsc
    > INNER JOIN vgt.gsc AS refgsc
    > ON refgsc.gsc_XX = refmgsc.id)
    > INNER JOIN vgt.geo AS refgeo
    > ON refgeo.id = refgsc.id
    > INNER JOIN vgt.B AS refB
    > ON refgeo.id = refB.id
    > INNER JOIN vgt.v_ecoclim AS refeco
    > ON refgeo.id = refeco.id
    > INNER JOIN vgt.v_vcf2001 AS refvcf
    > ON refgeo.id = refvcf.id
    > INNER JOIN (vgt.gba AS refgba
    > INNER JOIN vgt.meta_gba AS refmgba
    > ON refgba.gba_XX = refmgba.id)
    > ON refgeo.id=refgba.id
    > INNER JOIN vgt.NDVI AS refNDVI
    > ON refgeo.id = refNDVI.id
    > WHERE NDVI.id= 1133568
    > AND refgeo.X_coord > 1288
    > AND refgeo.X_coord < 1328
    > AND refgeo.Y_coord > 659
    > AND refgeo.Y_coord < 699
    > AND refmgba.burnt=0
    > AND refmgsc.burnt=0
    > AND refeco.v_landcov=eco.v_landcov
    > AND refeco.v_lowreb=eco.v_lowreb
    > AND refvcf.vcf_bare < vcf.vcf_bare+20
    > AND refvcf.vcf_bare > vcf.vcf_bare-20
    > AND refvcf.vcf_tree < vcf.vcf_tree+20
    > AND refvcf.vcf_tree > vcf.vcf_tree-20
    > AND refvcf.vcf_herb < vcf.vcf_herb+20
    > AND refvcf.vcf_herb > vcf.vcf_herb-20
    > GROUP BY refmgba.burnt
    >
    > I have thus several main-tables that have identical rows (and are
    > linked by id in a 1to1-relatioship ) and I have 2 sub-tables in a
    > manyto1-relationship. I have put it in a scheme below (view using
    > Courier font for correct spacing).
    >
    > The main tables are: NDVI, B, eco, vcf, refgsc, refgeo, refeco, refgba,
    > refB, refvcf, refNDVI.
    > The 2 subtables: refmgba, refmgsc.
    >
    > Is my query correct? Do you have any solutions to make my query more
    > efficient?
    > I've studied the mysql-manual, but I don't see answers on the moment
    >
    > Thanks in advance,
    > Stef
    >
    > Schematic overview tables:
    >
    > [=============] [=============] [============]
    > [T_Main: gsc ] [T_Main: gba ] [T_Main:NDVI ] O
    > [=============] [=============] [============] T
    > [ ID ]<--->[ ID ]<->[ ID ]<->H
    > |-->[ gsc_XX(many)] |->[ gba_XX(many)] [ NDVI_99_24 ] E
    > | [ Others... ] | [ Others... ] [ NDVI_99_25 ] R
    > | [=============] | [=============] [ Others... ] S
    > | | [============]
    > | |
    > | |
    > | [=============] | [=============]
    > | [T: refmgsc ] | [T: refmgba ]
    > | [=============] | [=============]
    > |--> [ ID(1) ] |--> [ ID(1) ]
    > [ BURNT ] [ BURNT ]
    > [ Others... ] [ Others... ]
    > [=============] [=============]
    >
    Dikkie Dik Guest

  4. #4

    Default Re: Optimize JOIN to gain speed

    Stefan Rybacki schreef:
    > >...
    > > FROM vgt.NDVI AS NDVI
    > > INNER JOIN vgt.B AS B
    > > USING (id)
    > > INNER JOIN vgt.v_ecoclim AS eco
    > > USING (id)
    > > INNER JOIN vgt.v_vcf2001 AS vcf
    > > USING (id)
    > > ,
    > > (vgt.meta_gsc AS refmgsc
    >
    > Whats this here? Seems to be a cross join. How do you combine the 3
    > combined tables with the ones below?

    I combine the 3 tables with the ones below based on the WHERE clauses.
    Since I am calculating a ratio, I use a subset of for example vgt.B as
    B in the nominator and a different subset of of vgt.B as refB in the
    denominator. The selection of the 2 subsets is based on the WHERE
    clause. Is this incorrect?

    >
    > > INNER JOIN vgt.gsc AS refgsc
    > > ON refgsc.gsc_XX = refmgsc.id)
    > > INNER JOIN vgt.geo AS refgeo
    > > ON refgeo.id = refgsc.id
    > > INNER JOIN vgt.B AS refB
    > > ON refgeo.id = refB.id
    > > INNER JOIN vgt.v_ecoclim AS refeco
    > > ON refgeo.id = refeco.id
    > > INNER JOIN vgt.v_vcf2001 AS refvcf
    > > ON refgeo.id = refvcf.id
    > > INNER JOIN (vgt.gba AS refgba
    > > INNER JOIN vgt.meta_gba AS refmgba
    > > ON refgba.gba_XX = refmgba.id)
    > >...
    stefaan.lhermitte@agr.kuleuven.ac.be Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Dot gain for Kinkos?
    By Tim_Lookingbill@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 6
    Last Post: February 12th, 02:12 PM
  3. Optimize CD-ROM speed?
    By Jack Suter in forum Macromedia Director Basics
    Replies: 1
    Last Post: December 6th, 04:17 PM
  4. Gray mode and dot gain
    By Peter Gaunt in forum Adobe Photoshop Elements
    Replies: 3
    Last Post: August 29th, 01:51 PM
  5. Replies: 1
    Last Post: August 15th, 11:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139