[mysql]: Querying more db data vs loop on large array

Question

I need to compare few scenarios, which can be fulfilled either by calling:

an additional timestamp column from mysql database or
Looping over the resultant array.

Elaborating further:
CASE 1: 144 byte columns + 4 byte timestamp column for 10K rows, then looping on array of size 50.(download size- 1480000 Bytes)
CASE 2: 144 byte columns for 10K rows, looping on array of size 10000. (download size- 1440000 bytes)

Data download roughly 40KB more for Case1 while 10000 more loop iteration for case2.

Which of the 2 scenarios could be faster, downloading 40KB more or 10000 loop iterations?

144 columns of 1 byte each? 1 column of 144 bytes? VARCHAR or something else? — Rick James
– Rick James, Commented Sep 30, 2022 at 15:53
"Download" -- SELECT? LOAD DATA? or something else? Is the download configured to fetch the entire resultset, then feed the rows to you one at a time? — Rick James
– Rick James, Commented Sep 30, 2022 at 15:56

O. Jones · Accepted Answer · 2022-09-29 19:01:25Z

2

Your first scenario is by far the best. Here is why.

SQL is designed to extract subsets of rows from tables. It's designed to allow your tables to be many orders of magnitude bigger than your RAM. If you use a query like

SELECT * 
  FROM mytable
 WHERE mytimestamp >= TODAY() - INTERVAL 1 DAY
   AND mytimestamp < TODAY()

you will get all the rows with timestamps anytime yesterday, for example. If you put an index on the mytimestamp column, SQL will satisfy this query very quickly and only give the rows you need. And it will satisfy any queries looking for a range of timestamps similarly quickly.

answered Sep 29, 2022 at 19:01

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2022-09-29 19:15:02Z

There are no answers that are true in 100% of situations. For example, I would prefer to do the first query when I use a fast enough network (anything 1Gbps or higher) and data transfer is free. The difference in the example you show is 40,000 bytes, but it's only 2.7% of the total.

On the other hand, if you need to optimize to minimize bandwidth usage, that's a different scenario. Like if you are transmitting data to a satellite over a very slow link. Or you have a lot of competing network traffic (enough to use up all the bandwidth), and saving 2.7% is significant. Or if you are using a cloud vendor that charges for total bytes transferred on the network.

You aren't taking into account the overhead of executing 1000 separate queries. That means 1000x the bytes sent to the database server, as you send queries. That takes some network bandwidth too. Also the database server needs to parse and optimize each query (MySQL does not cache optimization plans as some other RDBMS products do). And then begin executing the query, starting with an index lookup without the context of the previous query result.

"Performance" is a slippery term. You seem to be concerned only with bandwidth use, but there are other ways of measuring performance.

Throughput (bytes per second)
Latency (seconds per response)
Wait time (seconds until the request begins)

All of these can be affected by the total load, i.e. number of concurrent requests. A busy system may have traffic congestion or request queueing.

You can see that this isn't a simple problem.

Collectives™ on Stack Overflow

[mysql]: Querying more db data vs loop on large array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related