Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is using extra whitespace to make MySQL queries more readable inefficient to the point of not being worth it?

Lets say I have a query

UPDATE foo.bar SET crop=5 WHERE soil='brown'

I would write this as so:

UPDATE  
  foo.bar  
SET  
  crop = 5  
WHERE  
  soil = 'brown'  

I write queries in this format to make them more readable in my code. However, this is being transfered between a web server and a DB server, so I assume that the wasted space will take up some bandwidth and have some slowdown in transfer.

Assuming I am writing much larger queries, ones that take 20+ lines, is this inefficient to the point of not being worth it?

like image 618
user138821 Avatar asked May 10 '11 16:05

user138821


2 Answers

You should always write your queries such that they are maximally readable. If you are really worried about saving bytes transferred to and from the server you can run your queries through a database connector and set the options such that extraneous whitespace is removed. This might already be happening with your current code. Have you actually snooped on the SQL data that is being sent across the network to see what it looks like?

like image 172
Cyde Weys Avatar answered Sep 29 '22 11:09

Cyde Weys


No, there is no great difference in efficiency. Readability is the virtue that should win out. If you are really concerned with efficiency and you frequently use queries that differ only in parameter values, you could consider using prepared statements, for which the query execution plan is only calculated once (with a corresponding increase in efficiency).

like image 44
Hammerite Avatar answered Sep 29 '22 13:09

Hammerite