Optimizing MySQL performance

- Use of Index
- The EXPLAIN keyword - analyze the execution of a query.
- Avoid calculation on a field. e.g., use "rate < 40" instead of "rate / 2 < 20".
- MySQL uses leftmost prefixing, which means that a multi-field index A,B,C will also be used to search not only for a,b,c combinations, but also A,B as well as just A.
- The Query Optimizer, OPTIMIZE and ANALYZE.
  ANALYZE TABLE tablename 
  OPTIMIZE TABLE tablename 
- Use short index: index the first few characters of a field. e.g., ALTER TABLE employee ADD INDEX(surname(20),firstname(20));
- Load data in batch, instead of insert 1-by-1:
  $db->query("LOAD DATA INFILE 'datafile.txt' INTO TABLE employee (employee_number,firstname,surname,tel_no,salary) FIELDS TERMINATED BY '|'");
  LOAD DATA INFILE has defaults of:
  FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '' 

  INSERT LOW PRIORITY - insert only when no read.
  INSERT DELAYED - non-block insert, put insert requests on a queue.

- Fast delete: in MySQL 4.0 or later
  TRUNCATE TABLE classifieds; 
  runs faster than
  DELETE FROM classifieds;
  since "TRUNCATE" deletes all at once, but "DELETE" deletes one by one.

Reference:
[1] databasejournal.com: Optimizing MySQL: Queries and Indexes

Change forgotten postgresql root password

The root of postgresql is called 'postgres'. See details below:

Change forgotten PostgreSQL password

Some common postgresql commands:

- psql -l -- list all databases
- psql -U [user] -W -d testdb -- login using password

In psql shell:

- \l -- list all databases
- \du -- list users
- \c [database] -- switch to a database
- \dt -- list tables in a database
- \d [table] -- describe a table (\d+ does the same thing)
- \dv -- list views in a database

Check syntax: use "EXPLAIN". E.g.: EXPLAIN Select * from Table1

PostgreSQL ODBC driver (psqlODBC): Download here.

By default postgresql allows only local connection. To enable remote connection you need to change 2 configuration files [1]:
1) vi /var/lib/pgsql/data/pg_hba.conf
add this line: host all account_name 100.100.100.100/32 md5
2) vi /var/lib/pgsql/data/postgresql.conf
add this line: listen_addresses = '*'
Then you need to restart the postgresql server, similar to this:
sudo /sbin/service postgresql restart

[1] How Do I Enable remote access to PostgreSQL database server? [2] PostgreSQL SQL Syntax and Use