Coding Tips, Duke Experience

March 14, 2018

Database

1. Index the required columns from table.
Make sure the table columns are properly indexed as per the requirement of your query. Do not keep adding too many indices, as it takes up additional disk space.

2. Use signed/unsigned properly.
Signed variables use one bit to flag whether they are positive or negative. Unsigned variables don’t have this bit, so they can store larger numbers in the same space, but only non-negative numbers, e.g. 0 and higher. A developer looking at the structure of the table will get an idea of what possible value or ranges the field must store.

3. Use decimal to save prices.
The following is an excerpt from mysql documentation.

> The DECIMAL and NUMERIC types are used to store values for which it is important to preserve exact precision, for example with monetary data.

Extra care has to be taken if you are using Floating Point as the data type, as it can cause problems due to its rounding properties.

4. Set proper character set etc.
Maintain proper character set of client and server. For example, a client can be a CSV file having list of German names with accents. If the server (including the database), is not following the same charset as the csv file, then the accented characters may be saved as gibberish in the mysql table.

Code

1. IIFE – Immediately Invoked Function Expression.
Sometimes you need to define and call a function at the same time and only once.
IIFE also helps in keeping the global namespace clean and unpolluted.
Normal Way:

IIFE way:

2. Callback function
A callback function, also known as a higher-order function, is a function that is passed to another function (let’s say “otherFunction”) as a parameter. The callback function is then called (or executed) inside the otherFunction.

3. Do not need an ELSE for every IF
This depends on the situation for which you are writing the code. For obvious evaluations you do not need to add an ELSE for every IF.
In the below example, it makes no difference in adding 0 to make variable ‘a’, a positive number.
Recommended:

Not Recommended:

4. Function name should give 90% idea what a function does
There is nothing wrong in having a longer function name. If you are not able to name a function, then you yourself have not made up your mind what that function will do, and what code you will be writing in it.
For example a name like readPropertiesFromFileThenWriteToSession is better than ReadProps, because it reduces the need to look at the implementation of the function.
If you can’t describe in one word or a short phrase what the method is doing, split it into more methods.

5. Query Builder

> Programs must be written for people to read, and only incidentally for machines to execute. -Hal Abelson

Exploit the use of Javascript to write good looking code.
Sometimes we need to generate some HTML and append it to the DOM using Javascript(Ajax Call cases).

For example, to add a new row at the end of an existing table, we tend to create an HTML string in a very untidy format as seen below.

An alternative to achieve the same and what I learned is

MySQL Queries

1. Use EXPLAIN to write better MySQL queries
Many times it has happened that we need to run some heavy queries on the MySQL server directly to get some quick statistics about the project.
In such cases, if our query is not properly built, the query will keep getting executed without an end. This puts a tremendous load on the server speed and will create unnecessary mysql dump files.
In such situations, it is always good to run the same query with an EXPLAIN statement first.
Using an EXPLAIN statement to run a query returns a lot of valuable information about that query and the table(s) upon which the query will run.
It gives information about the presence of keys and indices of the tables. It gives you the count of total records the system will need to scan through and get the result of your query.
If you find something is off, then you should fix your query and once you are certain that query is good, you should run it on the server.

If you need to learn more about the EXPLAIN keyword(with example), please visit the below link.
https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

2. Performance of LIMIT OFFSET in MySQL Query
This is fast

This is much slower.

Higher offsets slows the query down.
MySQL cannot go directly to the 10000th record because it cannot assume it has continuous values in 1 to 10000. Although it might be that way in actuality, MySQL cannot assume that there are no holes/gaps/deleted ids.

So, MySQL will have to fetch 10000 rows (or traverse through 10000th entries of the index on id) before finding the 30 to return.

A solution to this is:
After every loop, save the last ID. You can store this in a temporary table or a variable within your code if possible.
Grab the last ID (for example lastID = 650), and assign it to some variable.