Professional Web Applications Themes

Improve SELECT command - MySQL

Dear MySQL-ians, I perform a SELECT on my database, but it takes over a minute for every run. I have to run it over 10000 times (with different values in the WHERE), so it takes way too long. A was therefore wondering if I could improve the query speed. Below you find the query. It is based on the ratio between a pixel (pix) vs. the average of its neighbourhood (from geo) in the same table (vgt) based on additional requirements (mgba,mgsc,eco). Any comment on the query/design is welcome. SELECT vgt.obs,pix.NDVI/AVG(vgt.NDVI) FROM vgtData.VGT as pix, (vgt.meta_gsc_2000 as mgsc RIGHT JOIN ...

  1. #1

    Default Improve SELECT command

    Dear MySQL-ians,

    I perform a SELECT on my database, but it takes over a minute for every
    run. I have to run it over 10000 times (with different values in the
    WHERE), so it takes way too long. A was therefore wondering if I could
    improve the query speed. Below you find the query. It is based on the
    ratio between a pixel (pix) vs. the average of its neighbourhood (from
    geo) in the same table (vgt) based on additional requirements
    (mgba,mgsc,eco).

    Any comment on the query/design is welcome.

    SELECT vgt.obs,pix.NDVI/AVG(vgt.NDVI)
    FROM vgtData.VGT as pix,
    (vgt.meta_gsc_2000 as mgsc RIGHT JOIN vgt.gsc2000 as gsc ON
    gsc.gsc2000_XX = mgsc.id)
    INNER JOIN vgt.geo_1000 as geo ON geo.id = gsc.id
    INNER JOIN vgtData.VGT as vgt ON geo.id = vgt.id
    INNER JOIN vgt.v_ecoclim AS eco ON geo.id = eco.id
    INNER JOIN (vgt.gba2000 as gba LEFT JOIN vgt.meta_gba_2000 as mgba ON
    gba.gba2000_XX = mgba.id) ON gba.id=geo.id
    WHERE pix.obs = vgt.obs
    AND pix.id = 200265
    AND vgt.obs > 55
    AND vgt.obs <= 127
    AND (geo.X_coord BETWEEN 1525 AND 1545)
    AND (geo.Y_coord BETWEEN 110 AND 130)
    AND mgba.burnt=0
    AND mgsc.burnt=0
    AND eco.v_landcov=3
    AND eco.v_lowreb=10
    AND vgt.B<27
    AND vgt.SWIR<250
    AND pix.B<27
    AND pix.SWIR<250
    GROUP BY vgt.obs;

    The EXPLAIN command gives me the following result:
    table type possible_keys key len ref
    rows Extra
    ----- ------ -------------------------- -------- -- --------------
    ----- --------------------------------------------
    eco ref PRIMARY,v_landcov,v_lowreb v_lowreb 3 const
    17021 Using where; Using temporary; Using filesort
    geo eq_ref PRIMARY,X_coord,Y_coord PRIMARY 3 eco.id 1
    Using where
    gsc eq_ref PRIMARY,gsc2000_XX PRIMARY 3 geo.id 1

    mgsc eq_ref PRIMARY,burnt PRIMARY 3 gsc.gsc2000_XX 1
    Using where
    vgt ref id,obs id 4 geo.id 1
    Using where
    pix ref id,obs id 4 const 156
    Using where
    gba eq_ref PRIMARY,gba2000_XX PRIMARY 3 geo.id 1

    mgba eq_ref PRIMARY,burnt PRIMARY 3 gba.gba2000_XX 1
    Using where


    and the tables look like;
    ################################################## #
    vgtData.VGT
    Field Type Null Key Default Extra
    ----- -------------------- ---- --- ------- ------
    id mediumint(8) unsigned YES MUL (null)
    obs tinyint(4) unsigned YES MUL (null)
    B tinyint(4) unsigned YES (null)
    SWIR tinyint(4) unsigned YES (null)
    NDVI tinyint(4) unsigned YES (null)
    ################################################## ##

    ################################################## #######
    vgt.geo_1000
    Field Type Null Key Default Extra
    ------ --------------------- ---- --- ------- ---------
    id mediumint(8) unsigned PRI (null) auto_incr
    X_coord smallint(6) unsigned YES MUL (null)
    Y_coord smallint(7) unsigned YES MUL (null)
    ################################################## #######


    ################################################## #######
    vgt.v_ecoclim
    Field Type Null Key Default Extra
    --------- --------------------- ---- --- ------- ---------
    id mediumint(8) unsigned PRI (null) auto_incr
    v_lowreb smallint(7) YES MUL (null)
    v_landcov smallint(7) YES MUL (null)
    ################################################## #######

    ################################################## #######
    vgt.gba2000
    Field Type Null Key Default Extra
    ---------- --------------------- ---- --- ------- ---------
    id mediumint(8) unsigned PRI (null) auto_incr
    gba2000_XX smallint(7) unsigned YES MUL (null)
    ################################################## #######

    ################################################## #######
    vgt.meta_gba_2000
    Field Type Null Key Default Extra
    ----- --------------------- ---- ---- ------- --------
    id mediumint(8) unsigned PRI (null) auto_incr
    burnt tinyint(1) YES MUL (null)
    ################################################## #######

    ################################################## #######
    vgt.gsc2000
    Field Type Null Key Default Extra
    ---------- --------------------- ---- --- ------- ---------
    id mediumint(8) unsigned PRI (null) auto_incr
    gsc2000_XX smallint(7) unsigned YES MUL (null)
    ################################################## #######

    ################################################## #######
    vgt.meta_gsc_2000
    Field Type Null Key Default Extra
    ----- --------------------- ---- ---- ------- --------
    id mediumint(8) unsigned PRI (null) auto_incr
    burnt tinyint(1) YES MUL (null)
    ################################################## #######

    I am introducing indexes on vgtData.SWIR and vgtData.B but they have a
    very low cardinality, so I assume it won't make a big difference.

    Does anyone has a suggestion to make it faster?

    Thanx in advance and kind regards,
    Stef

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

  2. #2

    Default Re: Improve SELECT command

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] wrote:
    > SELECT vgt.obs,pix.NDVI/AVG(vgt.NDVI)
    ....
    > GROUP BY vgt.obs;
    This is a bit odd, because you are not grouping by pix.NDVI. In some
    RDBMS implementations, it is mandatory to group by all columns mentioned
    in the select-list that are not used inside aggregate functions. MySQL
    permits this. However, the value it returns for pix.NDVI will be some
    arbitrarily chosen value in the group. Though in this case I assume you
    restrict the pix table sufficiently, so it doesn't cause any ambiguity.
    > The EXPLAIN command gives me the following result:
    > table type possible_keys key len ref
    > rows Extra
    > ----- ------ -------------------------- -------- -- --------------
    > ----- --------------------------------------------
    > eco ref PRIMARY,v_landcov,v_lowreb v_lowreb 3 const
    > 17021 Using where; Using temporary; Using filesort
    > geo eq_ref PRIMARY,X_coord,Y_coord PRIMARY 3 eco.id 1
    > Using where
    > gsc eq_ref PRIMARY,gsc2000_XX PRIMARY 3 geo.id 1
    >
    > mgsc eq_ref PRIMARY,burnt PRIMARY 3 gsc.gsc2000_XX 1
    > Using where
    > vgt ref id,obs id 4 geo.id 1
    > Using where
    > pix ref id,obs id 4 const 156
    > Using where
    > gba eq_ref PRIMARY,gba2000_XX PRIMARY 3 geo.id 1
    >
    > mgba eq_ref PRIMARY,burnt PRIMARY 3 gba.gba2000_XX 1
    > Using where
    The temporary table & filesort mentioned in the line for eco is probably
    the culprit. These are on-disk data operations, which is very slow
    compared to in-memory operations. The GROUP BY is probably requiring
    the temp table, because it isn't using the index on vgt.obs. You could
    try using "FORCE INDEX (obs)" to make it use that index. But I'm not
    sure that this would relieve the filesort, and it would also prevent use
    of the vg.id index.
    > Does anyone has a suggestion to make it faster?
    MySQL 5 _might_ deal with this better. In some cirstances, MySQL 5
    can use more than one index per table. MySQL 4.1 and earlier have a
    limitation of one index per table in a given query. MySQL 5 also has
    some improvements to the filesort algorithm.

    You could try increasing your key_buffer_size server parameter, so that
    it's large enough to contain all the indexes used (according to the
    EXPLAIN report). Also, preload the indexes.

    See
    [url]http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/index-preloading.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Improve SELECT command

    [email]stefaan.lhermitteagr.kuleuven.ac.be[/email] wrote:
    > Dear MySQL-ians,
    >
    > I perform a SELECT on my database, but it takes over a minute for every
    > run. I have to run it over 10000 times (with different values in the
    > WHERE), so it takes way too long. A was therefore wondering if I could
    ....
    > SELECT vgt.obs,pix.NDVI/AVG(vgt.NDVI)
    > FROM vgtData.VGT as pix,
    > (vgt.meta_gsc_2000 as mgsc RIGHT JOIN vgt.gsc2000 as gsc ON
    > gsc.gsc2000_XX = mgsc.id)
    ....
    Perhaps you can store the right join in a temporary table
    and reuse it for the subsequent queries.



    Bernhard Brueck Guest

  4. #4

    Default Re: Improve SELECT command

    Thanks Bill and Bernhard!
    I will have a look at your suggestions and see if it can improve my
    queries.

    Regards,
    Stef

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

  5. #5

    Default Re: Improve SELECT command

    Thanks Bill and Bernard.

    I am indeed upgrading my mysql version to 5.0 and hoping to use the
    improved selection criteria there.

    Thanks for your help!
    Stef

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

Similar Threads

  1. Replies: 3
    Last Post: August 17th, 02:20 PM
  2. creating new command, how to select entire sourcecode
    By Koesper in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 2
    Last Post: November 21st, 02:07 PM
  3. Replies: 0
    Last Post: September 24th, 03:24 AM
  4. Command to Select Mask won't work in F-MX
    By TDogg webforumsuser@macromedia.com in forum Macromedia Fireworks
    Replies: 0
    Last Post: July 21st, 05:51 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

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