Drop Schema

DROP SCHEMA (Transact-SQL)

DROP SCHEMA schema_name

To drop a schema, any objects (e.g.: tables) associated with the schema must be dropped first.

Some articles from CACM

On software engineering: The Risks Of Stopping Too Soon - By David Lorge Parnas
On publication system: Stop the Numbers Game - By David Lorge Parnas

CSE 231 - Advanced Compiler Design and Implementation

CSE 231 - Advanced Compiler Design and Implementation.

Pay attention to the link "List of Papers for Presentations". These are papers from the most prestigeous conferences on compiler up to 2002.

MSSQL server memory setting

Suggested Max Memory Settings for SQL Server 2005/2008
Server Memory Options

MSSQL server memory setting

Suggested Max Memory Settings for SQL Server 2005/2008
Server Memory Options

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