Professional Web Applications Themes

Why is this that complicated? - MySQL

I have a table and I have to do a certain mathematical calculation for each row. Than I have to select the row with the minimal result of calculation. I don't want to do the calculation more than once and I don't want to sort the whole table. I did the following: CREATE TEMPORARY TABLE Temp SELECT Transformers.*, SQRT(POW(1234567 - Transformers.X, 2) + POW(7654321 - Transformers.Y, 2)) AS Distance FROM Transformers; SELECT * FROM Temp WHERE Distance = (SELECT MIN(Distance) FROM Temp); DROP TABLE Temp; This looks nice, but it doesn't work since I can execute only one SELECT on ...

  1. #1

    Default Why is this that complicated?

    I have a table and I have to do a certain mathematical calculation for
    each row.
    Than I have to select the row with the minimal result of calculation.

    I don't want to do the calculation more than once and I don't want to
    sort the
    whole table.

    I did the following:


    CREATE TEMPORARY TABLE Temp
    SELECT Transformers.*,
    SQRT(POW(1234567 - Transformers.X, 2) + POW(7654321 - Transformers.Y,
    2)) AS Distance
    FROM Transformers;
    SELECT * FROM Temp
    WHERE Distance = (SELECT MIN(Distance) FROM Temp);
    DROP TABLE Temp;


    This looks nice, but it doesn't work since I can execute only one
    SELECT
    on temporary table. Otherwise, temporary tables would fit perfectly
    since
    this request is repeated frequently (with input different numbers) and
    for
    multiple sessions.


    How to solve this problem?

    Many thanks.

    nte@index.hr Guest

  2. #2

    Default Re: Why is this that complicated?

    On Feb 6, 8:45 am, hr wrote: 

    Sorry if this obvious but would a JOIN solve the problem?

    strawberry Guest

  3. #3

    Default Re: Why is this that complicated?

    hr wrote: 


    I have a couple of tips:

    1. If SQRT(expr1) < SQRT(expr2), then expr1 < expr2. So computing the
    SQRT() on both rows just for the purpose of finding the row with the
    least value is expensive and unnecessary. Eliminating this calculation
    could make the query cheap enough that you can do it on every request.

    2. The problem you have shown indicates that you calculate the distance
    to a fixed point for all rows. If that were the case, you could
    pre-calculate the distance and store it in a new column in the
    Transformers table. Put an index on it, and it would be very fast to
    query it thus:

    SELECT * FROM Transformers ORDER BY distance ASC LIMIT 1

    Anytime you insert a new row or change the X and Y, you would
    re-calculate the distance for just one row and store it. Anytime you
    change the fixed point you would need to re-calculate the distance for
    all rows.

    But I would assume that you don't use a fixed origin point for the
    distance calculation. It's more likely that you are comparing all rows
    against a different point from time to time. So this solution may not
    be appropriate in your case.

    3. You could store an additional non-temporary table for all distances
    from all rows to all fixed points that you may be interested in. This
    table would become very long, but just storing a few numbers, the table
    rows would not be very wide. With proper indexes it could still be
    searched quickly.

    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. for( ).....complicated..
    By UsualSuspect in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: March 5th, 01:20 PM
  2. A bit more complicated....
    By Dave Simon webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 3
    Last Post: January 10th, 05:59 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