0

I have almost lakhs of records in DB....I want to select the records based on some conditions..like

$val is an array...so I have some options like this

using for loop

for(){
SELECT * FROM TABLE WHERE ID IN($val[$i]);
}

otherwise concatenation ...like 

$value=$val[$i].','.$val[$i];

SELECT * FROM TABLE WHERE ID IN($value);

or I can go for OR condition 

SELECT * FROM TABLE WHERE(ID=$value1 OR ID=$value);

Which is the best solutions??

shanmugavel
  • 199
  • 1
  • 3
  • 13
  • 1
    I'd go for solution 2 because it is meant exactly for that. If you're worried about performance: TEST IT, MEASURE IT. Make a test and measure all options you have. Based on the information you're giving (lacking information on indices, table structure etc.) you're not going to get a "one solution fits all" answer. Having said that, I think it's obvious that option 1 takes a lot of round-trips to the server so will (most likely?) always be outperformed by the other two options. – RobIII Jul 16 '12 at 16:45

2 Answers2

1

Option 1 is a lot slower than options 2 and 3. One query is always faster. This way you save time on network communication with the server which takes most time of the query for simple(properly designed) queries.

To choose between options 2 and 3 see MYSQL OR vs IN performance

Community
  • 1
  • 1
Sergey Eremin
  • 10,994
  • 2
  • 38
  • 44
0

It depends on what you need to do.

If you want to process each result set individually I'd use the for approach.

If you need to process all the data together, I'd go with the in approach.

Either way, check that your table is properly indexed.

Barranka
  • 20,547
  • 13
  • 65
  • 83
  • I've checked the link posted by @kgb, and I agree... the `in` approach is better. But, once again, if you need to process each result set individually, you may be forced to use the `for` option. – Barranka Jul 16 '12 at 16:50
  • 1
    If you need to process each result set individually you still should (or could) use option 2 or 3; all you'd have to do is make the code a little smarter to determine which set you're actually processing. Only for large (as in: many fields, lots of data) resultsets it might be beneficial to process the resultset(s) in "chunks". – RobIII Jul 16 '12 at 16:50