10 MySQL Best Practices for Optimization

Posted on

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
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";
Free Hit Counters