Professional Web Applications Themes

select .. where ..in TOO LONG - MySQL

I need to perform a select based on a list of id's , it's fine when the number of id's is not too big, but when It can be very large.. what options could I have ? create a temporary table and match with this table ... each time I need to perform the select... ? no better option in term of performances ? thanks for yoru lights joss...

  1. #1

    Default select .. where ..in TOO LONG

    I need to perform a select based on a list of id's , it's fine when the
    number of id's is not too big, but when It can be very large..

    what options could I have ?

    create a temporary table and match with this table ... each time I need
    to perform the select... ?
    no better option in term of performances ?

    thanks for yoru lights

    joss

    Josselin Guest

  2. #2

    Default Re: select .. where ..in TOO LONG

    Strange problem to have. I would nuke a view everytime since i think you
    would get better string length in a view. And then select the view.
    Assumming nonquery can take larger sql statement then datareader.




    "Josselin" <fr> wrote in message
    news:4587b04c$0$27412$orange.fr... 


    Jared Guest

  3. #3

    Default Re: select .. where ..in TOO LONG

    Josselin wrote: 

    The method used depends on how you choose which ID's to look for. If the data is
    derived from the database and not user input, then you can use a derived table -
    basically a view on-the-fly:

    select field1,field2 from tablea where id in (select id from table2 where
    somevalue=somecriteria);

    or

    select temptab.field1, temptab.field2 from (select id, field1,field2 where
    somevalue=someothervalue) temptab ;

    or use a join

    select a.field1, b.field2 from table1 a join table2 b on a.id=b.id
    where b.status = 1;

    You have a database engine and these are just starting points...

    --
    Michael Austin.
    Database Consultant
    Michael Guest

  4. #4

    Default Re: select .. where ..in TOO LONG

    On 2006-12-20 04:28:37 +0100, Michael Austin <com> said:
     
    >
    > The method used depends on how you choose which ID's to look for. If
    > the data is derived from the database and not user input, then you can
    > use a derived table - basically a view on-the-fly:
    >
    > select field1,field2 from tablea where id in (select id from table2
    > where somevalue=somecriteria);
    >
    > or
    >
    > select temptab.field1, temptab.field2 from (select id, field1,field2
    > where somevalue=someothervalue) temptab ;
    >
    > or use a join
    >
    > select a.field1, b.field2 from table1 a join table2 b on a.id=b.id
    > where b.status = 1;
    >
    > You have a database engine and these are just starting points...[/ref]

    thanks ..
    the data is.. well ... not actually derived from a DB it's coming from
    a serialized Ruby Array of arrays , stored into a text column (km25)
    in a table 'cities' (I am using Rails..) , the original array is like
    [ [ 23455, 2.45025], [ 45896, 4.56876], ..... ]
    km25 means 'all cities around 25 km) , each array store the id of the
    city (int) and the km- distance (float).

    Getting a current_city from the table in memory, brings this data ,
    getting immediatly all the id's/distance of all cities around 25km
    makes a BIG difference in performance vs calculating it from the
    LAT-LONG data
    then I need to extract 'proposals' for the current city (one-to-many)
    AND from the cities in this list... that's why I am using the SELECT ..
    WHERE cities_id IN (the array of id's)....

    drawback : I had to run a batch app to perform the calculous for all
    the cities in the table (40'000 ) and it tooks many hours... but these
    data will never change... no new city.. and the distances are
    immutable (at least until the global earth warming... ;))))

    It's running fine now .. ! I got it

    joss

    Josselin Guest

Similar Threads

  1. Question about a long session timeout (somewhat long)
    By Stupid48 in forum ASP.NET Security
    Replies: 7
    Last Post: March 1st, 10:04 PM
  2. Replies: 8
    Last Post: November 15th, 07:08 PM
  3. Can PS7 Select "long side" when Batch Resizing?
    By Mike McBride in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 4
    Last Post: March 6th, 04:04 AM
  4. strtotime and dates long long ago ...
    By ruud in forum PHP Development
    Replies: 2
    Last Post: December 29th, 05:48 AM
  5. Replies: 8
    Last Post: September 20th, 07:59 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