MySQL Query Optimization Tips and Techniques

1. Choosing Which Version of MySQL
2. Don't join extra tables in MySQL Query.
3. Only get N records which are going to used. Don't get extra records from table which you are not using.
4. Don't fetch extra column from table which are not in use.
5. Don't add spam records in tables
6. Don't get it overload any table like 1GB Size, In this case shift data to another table.
7. Get the understanding of normalization.
8. Use Explain/DESCRIBE to know about table structure and Query. For Example:

DESCRIBE SELECT * FROM `users` WHERE username='web-tech' 

9. Partition your table (MySQL 5.1). Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key.
10. Partition can be achieve in three way 1. RANGE, HASH and List/Key.
11. Build your indexes to match the queries running. For Example:

    ALTER TABLE `users` ADD INDEX `profile_id` (`profile_id`)

    Know about MySQL Indexing
    
12. Use concatination indexing, if required (When searching like below).

    SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';

13. No indexing on column which not used in search.
14. Sometime you need to use OPTIMIZE Table. For Example:

    OPTIMIZE TABLE `users`

    Used for defragment tables and update the InnoDB fulltext index
15. Full Text Search can be used, if required.
    http://www.web-technology-experts-notes.in/2013/05/mysql-fulltext-search.html

Best Related Posts are Following:

1. Why error 524 a timeout occurred.
2. Free Boost Web Application By 20% in Simple Steps.
3. Improve Ajax Performance.
4. Reduce the loading time of website.

How to detect Mobile Device in PHP? - Simplest way

Question: What does this script do?
It simply detect the request and tells request comes from mobile browser (Mobile devices) OR Web Devices (Desktop, Laptop, Ipad).


Question: What are Server Requirements for this script?
PHP Must be installed.


Question: Why we do need this script?
Sometime we need to give the different response on the behalf of devices.
For Example:
We give 20 records for web browser.
We give 10 records for Mobile browser.


Question: Where we do need this script? Give Example?
We need this script where we change the response on the behalf of devices browser.
For Example:

1. Different number of record in web and in mobile.
2. In Responsive website, we hide the extra information which is not displayed in mobile devices. In this case we should also not fetch these details from the server.
3. Display the different sizes of images on the behalf of device.
4. If website is not compatiable to mobile devices, Give a good message to users.

Question: How to detect the mobile devices?

$useragent=$_SERVER['HTTP_USER_AGENT'];
$isMobile=0;
if(preg_match('/(android|bb\d+|meego).+mobile|avantgo|bada\/|blackberry|blazer|compal|elaine|fennec|hiptop|iemobile|ip(hone|od)|iris|kindle|lge |maemo|midp|mmp|netfront|opera m(ob|in)i|palm( os)?|phone|p(ixi|re)\/|plucker|pocket|psp|series(4|6)0|symbian|treo|up\.(browser|link)|vodafone|wap|windows (ce|phone)|xda|xiino/i',$useragent)||preg_match('/1207|6310|6590|3gso|4thp|50[1-6]i|770s|802s|a wa|abac|ac(er|oo|s\-)|ai(ko|rn)|al(av|ca|co)|amoi|an(ex|ny|yw)|aptu|ar(ch|go)|as(te|us)|attw|au(di|\-m|r |s )|avan|be(ck|ll|nq)|bi(lb|rd)|bl(ac|az)|br(e|v)w|bumb|bw\-(n|u)|c55\/|capi|ccwa|cdm\-|cell|chtm|cldc|cmd\-|co(mp|nd)|craw|da(it|ll|ng)|dbte|dc\-s|devi|dica|dmob|do(c|p)o|ds(12|\-d)|hp( i|ip)|hs\-c|ht(c(\-| |_|a|g|p|s|t)|tp)|el(49|ai)|em(l2|ul)|er(ic|k0)|esl8|ez([4-7]0|os|wa|ze)|fetc|fly(\-|_)|g1 u|g560|gene|gf\-5|g\-mo|go(\.w|od)|gr(ad|un)|haie|hcit|hd\-(m|p|t)|hei\-|hi(pt|ta)|hu(aw|tc)|i\-(20|go|ma)|i230|iac( |\-|\/)|ibro|idea|ig01|ikom|im1k|inno|ipaq|iris|ja(t|v)a|jbro|jemu|jigs|kddi|keji|kgt( |\/)|klon|kpt |kwc\-|kyo(c|k)|le(no|xi)|lg( g|\/(k|l|u)|50|54|\-[a-w])|libw|lynx|m1\-w|m3ga|m50\/|ma(te|ui|xo)|mc(01|21|ca)|m\-cr|me(rc|ri)|mi(o8|oa|ts)|mmef|mo(01|02|bi|de|do|t(\-| |o|v)|zz)|mt(50|p1|v )|mwbp|mywa|n10[0-2]|n20[2-3]|n30(0|2)|n50(0|2|5)|n7(0(0|1)|10)|ne((c|m)\-|on|tf|wf|wg|wt)|nok(6|i)|nzph|o2im|op(ti|wv)|oran|owg1|p800|pan(a|d|t)|pdxg|pg(13|\-([1-8]|c))|phil|pire|pl(ay|uc)|pn\-2|po(ck|rt|se)|prox|psio|pt\-g|qa\-a|qc(07|12|21|32|60|\-[2-7]|i\-)|qtek|r380|r600|raks|rim9|ro(ve|zo)|s55\/|sa(ge|ma|mm|ms|ny|va)|sc(01|h\-|oo|p\-)|sdk\/|se(c(\-|0|1)|47|mc|nd|ri)|sgh\-|shar|sie(\-|m)|sk\-0|sl(45|id)|sm(al|ar|b3|it|t5)|so(ft|ny)|sp(01|h\-|v\-|v )|sy(01|mb)|t2(18|50)|t6(00|10|18)|ta(gt|lk)|tcl\-|tdg\-|tel(i|m)|tim\-|t\-mo|to(pl|sh)|ts(70|m\-|m3|m5)|tx\-9|up(\.b|g1|si)|utst|v400|v750|veri|vi(rg|te)|vk(40|5[0-3]|\-v)|vm40|voda|vulc|vx(52|53|60|61|70|80|81|83|85|98)|w3c(\-| )|webc|whit|wi(g |nc|nw)|wmlb|wonu|x700|yas\-|your|zeto|zte\-/i',substr($useragent,0,4))){
    $isMobile=1;
}

if($isMobile){
    /** Script For Mobile **/

    /** Script For Mobile **/
}else{
    /** Script For Web**/

    /** Script For Web**/
}

Best Related Posts are Following:

1. How to Create Thumbnail Images of uploaded image using PHP.
2. Git interview questions and answers - Problem and Solutions.
3. How to import csv file to database through phpmyadmin.
4. How to export mysql query results to csv?.
5. MySql replace NULL values with empty string without effecting Rowset.
6. How to fix Headers already sent error in PHP?.
7. Twitter login failed leads to Page not found.
8. How to send an email using Email Template in Zend Framework.
9. How do I get wordpress global variable and functions in custom file [SOLVED].
10. How to convert php array into javascript array.