Best practices for MySQL performance optimization and MySQL use indexes

$r = mysql_query( "SELECT username FROM user WHERE signup_date >= CURDATE()" );// Turn on query cache$today = date ( "Ymd" );$r = mysql_query( "SELECT username FROM user WHERE signup_date >='$today'" );
$r = mysql_query( "SELECT * FROM user WHERE country ='China'" );
if (mysql_num_rows( $r )> 0) {
// ...
}

// efficient:
$r = mysql_query( "SELECT 1 FROM user WHERE country ='China' LIMIT 1" );
if (mysql_num_rows( $r )> 0) {
// ...
}
$r = mysql_query("SELECT company_name FROM usersLEFT JOIN companies ON (users.state = companies.state)WHERE users.id = $user_id ");// The two state fields should have been indexed, and they should be of the same type and the same character set.
$r = mysql_query( "SELECT username FROM user ORDER BY RAND() LIMIT 1" );// This would be better:$r = mysql_query( "SELECT count(*) FROM user" );$d = mysql_fetch_row( $r );$rand = mt_rand(0, $d [0]-1);$r = mysql_query( "SELECT username FROM user LIMIT $rand, 1" );
$r = mysql_query( "SELECT * FROM user WHERE user_id = 1" );$d = mysql_fetch_assoc( $r );echo "Welcome {$d['username']}" ;// recommended$r = mysql_query( "SELECT username FROM user WHERE user_id = 1" );$d = mysql_fetch_assoc( $r );echo "Welcome {$d['username']}" ;
if ( $stmt = $mysqli ->prepare( "SELECT username FROM user WHERE state=?" )) {

// bind parameters
$stmt ->bind_param( "s" , $state );

// carried out
$stmt ->execute();

// binding result
$stmt ->bind_result( $username );

// move the cursor
$stmt ->fetch();

printf( "%s is from %s\n" , $username , $state );

$stmt ->close();
}
"Mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed."
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id" ;
while (1) {//Only make 1000 each timemysql_query( "DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000" );if (mysql_affected_rows() == 0) {// Nothing to delete, exit!break ;}// take a break every timeusleep(50000);}
1. MySQL forces the use of indexes: force index (index name or primary key PRI)E.g:select * from table force index(PRI) limit 2; (mandatory use of primary key)select * from table force index(pankaj1_index) limit 2; (mandatory use of index "pankaj1_index")select * from table force index(PRI,pankaj1_index) limit 2; (mandatory use of index "PRI and pankaj1_index")2. MySQL prohibits an index: ignore index (index name or primary key PRI)E.g:select * from table ignore index(PRI) limit 2; (primary key is prohibited)select * from table ignore index(pankaj1_index) limit 2; (prohibit the use of index "pankaj1_index")select * from table ignore index(PRI,pankaj1_index) limit 2;(Prohibit the use of index "PRI,pankaj1_index")

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pankaj kushwaha

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS