0

I have the following SQL code which works fine on small datasets. I have a friend who told me I should look into putting this into a FOR loop in MySQL using a SELECT DISTINCT c.name as the looping condition.

SELECT c.name, c.limit, c.price, c._low, c._high, c.date
FROM comp c
WHERE c.e_id IN 
(SELECT e_id FROM part_e WHERE core_id=23)
OR c.pn IN
(SELECT pn FROM part_ct WHERE core_id=23)
GROUP BY c.name
ORDER BY c.date DESC, c.name ASC
LIMIT 0,200

As a new SQL programmer, I was looking up how to do loops in MySQL. While it seems simple enough conceptually, I am having a hard time trying to figure out how to actually design the SQL statement to actually run this in a loop.

My friend told me that the reason you should use a loop is that using 'post' operators on SQL datasets can have negative effects on performance either when dealing with extremely large datasets or when dealing with extremely large query requests.

This makes sense to me, so I am looking for some help in understanding how to do this, and more information as to why subqueries are considered bad practice.

Thanks for your help everyone!

Jim P
  • 534
  • 2
  • 8
  • 24

1 Answers1

0

Can you please explain what you mean with "for" and "loop"

In SQL you get a lot of data with the Select string, this data you have to use in you code (php,c,java...) and there you go from line to line oder dataset to dataset till it is finished

Perfomance hints: SQL Joins Vs SQL Subqueries (Performance)?

A tip: You Should group by c.e_id (it's faster to group by numbers)

Community
  • 1
  • 1
timod
  • 585
  • 3
  • 13
  • By for loop, I mean utilizing MySQL's ability to process large chunks of data internally instead of sending huge resultsets back. (http://dev.mysql.com/doc/refman/5.0/en/loop-statement.html) Additionally, I have to group by c.name because some things are in the part_ct table and others are in the part_e table, which do not have anything in common. – Jim P Jun 20 '12 at 20:53