10 MySQL Best Practices for Optimization

Posted on Jun 26, 2013

1. LIMIT 1 When Getting a Unique Row in your table

Sometimes when you are querying your tables, you already know you are looking for just one row. You might be fetching a unique record, or you might just be just checking the existence of any number of records that satisfy your WHERE clause.

In such cases, adding LIMIT 1 to your query can increase performance.
// do I have people from California?

// NOT to Do!:
$r = mysql_query("SELECT * FROM user WHERE state = 'California'");
if (mysql_num_rows($r) > 0) {
// ... other code
}

// Good request
$r = mysql_query("SELECT 1 FROM user WHERE state = 'California' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ... other code
}

MySQL dumps with PHP - Good Tips

Posted on Jun 24, 2013
To make a MySQL Dump with PHP, you'll simply have to execute the following Command in PHP script:
(The code below as one line in your PHP-script).
passthru("/usr/bin/mysqldump --opt --host=$dbhost --user=$dbuser
--password=$dbpwd $dbname > $dump_file_name");

MYSQL-HOST = MySQL server adress (mysql15**.examplehost.net).
USERNAME = the username for the database.
PASSWORD = the password for the database.
DATABASENAME = the name on the database.

Another bit of code that does the same job, better coded maybe.
<?php
$dbhost   = "mysql15**.examplehost.net";
$dbname   = "DATABASENAME";
$dbuser   = "USERNAME";
$dbpwd    = "PASSWORD";
$dumpfile = $dbname . "_" . date("Y-m-d_H-i-s") . ".sql";

© Twiwoo 2020 Cookie Policy