Macronimous Web Design

Have you thought about the right Data type which you should use?
Is VARCHR bad? Do you review the SQL statements that you have written? Do you know what sort of impact indexes produce? How to leverage the query Caching techniques? Do you follow the naming standards?

This simple slide teaches you the best mySQL practices to be followed up within 15 minutes.

Taking out the best from mySQL will improve the application performance, if its part of your day to day life get into these standards and practice the everyday. This presentation s from Ronald Bradford. Thanks to Ronald for sharing.

Index on multiple columns for SQL performance

Since clustered index reorders the table and arranges the data according to the index key, SQL Server allows only one clustered index per table. This results in amazing performance, when you only have to worry about one particular column. If you are in need to order the data by more than one column. Since we can’t use clustered index for multiple columns, unclustered index are used to gain a performance increase. We can specify a key value for every unclusterd index, plus the value obtained from cluster index; which acts as a pointer to the actual values when we need their data. If unclustered index is keyed on the values needed for a given task then query engine will never be in need of visiting the actual rows. After learning everything it needs from the index and scope then it will visit the qualifying rows.

1) Increase SQL Server stored procedure performance with following three tips:

        Stored procedures plays a vital role in enhancing the performance of the database. The following three tips will help us to maximize database performance, when stored procedures are used.

Use NOCOUNT:
           If NOCOUNT option is turned off, on each operation: stored procedures returns information concerning the number of rows affected and it increases network overhead. By default the Option NOCOUNT will be turned off.  If we turn on NOCOUNT option, stored procedures will block row count information so that it will reduce the network overhead occupied with information communication to the user.

Use return values:
           Queries are used to validate information or to return a single value. The return value of stored procedure can be used to retrieve a single value from a query and mainly useful when inserting a new record. The information necessary for retrieval is the primary key value. Place the statement “RETURN <value>” at the end of the query to utilize the return value in stored procedure.                 
        
Optimize table access with NOLOCK:
          Transaction safety is not mandatory for most of the database access. This is apparent in MySQL database product that it does not contribute any record locking capability. Any access to a database table or a stored procedure in SQL will make tremendous performance gains when a table hint is used, which lets the SQL engine ignore and not perform locks for a given function.

The Problem with all the codes is that the value is not sanitized before it will be sent as a query. All we need to make sure is that we passing the secure data into database. We can send secure data and prevent the data hacking by following the four prime SQL injection methods

Function mysql_real_escape_string() :

In php, we have a function to deal with strings in MYSQL. (Modified Content)

Function:
mysql_real_escape_string()

Above function seize the string that will be used in the MYSQL query and return the same string with all SQL Injection attempts securely escaped. Above functions will assist to replace each troublesome quotes in SQL Injection query with “backslash \”

Magic Quotes:

Magic quotes will helps to escape from risky form data that is used in SQL Injection. It will automatically include “backslash \” for each special characters in SQL Injection query submitted.

Function to check whether Magic quotes are enabled on server is
“get_magic_quotes_gpc function”

Example:
After adding magic quotes
The   \  becomes  \\
The   ‘  becomes  \’
The   “  becomes  \”

HTML Entities:

HTML Entities function translates all applicable characters to HTML Entities and returns the encoded string.

Function used to translate is,

string htmlentities ( string $string [, int $quote_style=ENT_COMPAT [, string $charset [, bool $double_encode=true ]]] )

Example: 

 <?php $str = “A ‘quote’ is <b>bold</b>”;

// Outputs: A ‘quote’ is &lt;b&gt;bold&lt;/b&gt;
echo htmlentities($str);

Validation:

Length Validation: Hamper of all the input fields in the application to the absolute minimum (7 to 15 characters). This will helps to block long queries input.
Input Validation: Validate the data entered in the input field. For eg. Age field should accept only number and only 2 digits are allowed.
User Privileges:  Create “Admin user” for each database and provide “create, drop and edit ” tables privileges only to the “admin user “

Briefed below are four MySQL tools that are really interesting, and you would like to review them and test them for better DB management experience. They do fairly good job with the database admin management, and good alternates to phpMyadmin. 
 
1. SQL Buddy: A tool that allows you to get more work done for less, the SQL Buddy tool though not very colorful and flashy, is very easy to navigate and fast loading as well. This saves a lot of time, and also allows using it the moment it is saved in the system. Also transferring of data between servers and .csv file export is also possible here.
 
2. Sidu: Another database admin tool with some wonderful features in it, including filtering and segregating of table list of a database. Also free controlling of table grouping, depending upon first few characters of the table name is made possible. This tool also assists in table access using the GUI feature. Data import is also possible to be done easily here.
 
3. My DB Studio: One of the best features that MyDB possesses is that it assists in SSH tunneling that helps in securing one’s connections, along with backup and restore database options. The tool also provides functionalities like creating, altering and deleting tables using WYSIWYG. The tool also aids in creating complete PHP scripts. This tool can be downloaded here.
 
4. MySQL Front: Another GUI for MySQL, this tool has complete control over the graphic display giving us more options to present the information clearly. One of the highlights of this tool is that it can make direct contacts with the database, thereby storing login ids and passwords, in your hard disk. This saves you from losing time spent to login at various web interfaces. Basically, this tool is helpful for those designers who are not very lettered about databases and only have a basic idea about it.