Why extra spaces and linebreaks in queries are bad?

I see, from time to time, that people say that SQL query that is sent to a server from client application should not contain any extra linebreaks or spaces. One of the reason I've heard is "why waste network traffic?".

Is there a real reason to make code harder to read and edit in favor of removing all spaces?

With spaces:

$q = 'SELECT
            `po`.*,
            `u`.`nickname`,
          开发者_如何学运维  `u`.`login`
        FROM
            `postponed_operations` AS `po`
            LEFT JOIN `users` AS `u` ON `u`.`id` = `po`.`user_id`
        ORDER BY `will_be_deleted_after`';
return mysql_query($q);

Without spaces:

$q = 'SELECT '.
            '`po`.*,'.
            '`u`.`nickname`,'.
            '`u`.`login`'.
        'FROM '.
            '`postponed_operations` AS `po` '.
            'LEFT JOIN `users` AS `u` ON `u`.`id`=`po`.`user_id` '.
        'ORDER BY `will_be_deleted_after`';
return mysql_query($q);

It is true, it will cost network traffic and server time; but it will be negligible on all except the most extreme cases. Now, if you are editing the code of FaceBook (or Google, or similar), and optimize in this way the 10 most common queries, then there is a point, since they will be run billions of times per day. But in all the other cases I think it is a waste of time to consider removing spaces.

This is subjective, but readability beats the few extra spaces and line breaks anytime in my opinion. And if coding standards would dictate to break of out the string every time, I'd probably go insane.

If you absolutely must optimize spaces and such away, do not do it in your source code. Instead put it through an automated intermediate tool.

If we were talking about web, I'd say that the extra cost in doing might potentially be worth it for static content (script files that rarely change and such) but I would be skeptical about doing it for dynamic content.

In all cases:

• If you change the source, it will be a maintenance nightmare.
• If you put it through a compression/decompression tool, you'll save significantly more (on average) than simply removing spaces but at a cost of latency and CPU time.
• Unless you have some really pathological structure, it basically constitutes a tiny fraction compared to the total cost, even if we only considered the size of TCP packets, and query data returned.

Perhaps not relevant in your case, but I'll mention it anyway: a completely different approach might be to use a tightly packed message format instead with a query ID, instead of transferring the query every time.

Absolutely. I do that all the times. I also:

• remove backquotes. Who needs them?

Therefore,

`po` ----- becomes -----> po 

• use as small as possible names for databases, tables, fields, indices, etc.

Therefore,

postponed_operations  --- becomes ---> po    --- p is already taken for posts
will_be_deleted_after --- becomes ---> wi    --- w is already taken for words

• Completely drop unnecessary keywords like AS. All table names are short anyway (rule 2 !)

Therefore,

LEFT JOIN `users` AS `u`  --- becomes --->   LEFT JOIN u 

As a result, I would have written the above query as:

$q='SELECT po.*,ni,lo FROM po LEFT JOIN u ON i=ui ORDER BY wi'

Tags:

joke

Although it is true that removing the unnecessary spaces and line breaks would reduce the amount of data that you send to the database server, but you should not bother about that.

Rather you should bother about the Readability and maintainability of the code. These are the two very important things you need to keep in mind while writing software code!

If reducing network traffic was the only good thing, then we could argue that you should make a Stored Procedure for every query that you write.

For eg. You could change the following query

SELECT
            `po`.*,
            `u`.`nickname`,
            `u`.`login`
        FROM
            `postponed_operations` AS `po`
            LEFT JOIN `users` AS `u` ON `u`.`id` = `po`.`user_id`
        ORDER BY `will_be_deleted_after`;

to

CALL GetLoginData();

Now that would be ~80-95% reduction. But is it worth it?

Definitely No.

Doing things like these would rather make the developers' life miserable without adding any significant value!

That being said, use minified version of code only at places where nobody would be changing the code. For eg. CSS libraries and JS libraries that you wont changing ever!

I hope you got the point, and you will continue to write Readable and Maintainable code!