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

Use ADODB.RecordSet in C#

Usually one can use System.Data.SqlClient to deal with operations on MSSQL server. The corresponding objects are SqlConnection, SqlCommand, SqlDataReader etc.

There is one scenario like this: the user needs to read something from each row in a table, and use the obtained information to update that row. One needs to cycle through all the records, and then use "UPDATE" query for the purpose. If SqlDataReader is used, the efficiency will be slow. SqlDataReader is read only, and uses the current connection exclusively as well. The user needs to open a second connection, and use the second connection to do the update based on a key.

In ADODB.RecordSet, one can use RecordSet.Update() function to directly update the current row and write back to database. No second connection is needed, and no key-based query is needed. This is much more efficient. A similar mechanism in SqlClient may exist, but I don't know yet at this time.

The following is an example using ADODB.RecordSet in C#. Note one needs to add ADODB to the references list of the current C# project first.

Parameters used by RecordSet can be found at http://www.w3schools.com/ADO/met_rs_open.asp.

private void updateTbl_ADODB() {
    ADODB.Connection conn = new ADODB.ConnectionClass();
    ADODB.Recordset rs = new ADODB.RecordsetClass();

    try {
        string strConn = 
            "Provider=SQLOLEDB;Initial Catalog=[database];Data Source=[server];";
        conn.Open(strConn, [user], [pwd], 0);

        string sql = "SELECT * FROM tbl";
        rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, 
                ADODB.LockTypeEnum.adLockOptimistic, -1);

        while (rs.EOF == false)  {
            rs.Update("field_name", [new_value]);
            rs.MoveNext();
        }

    } catch (Exception e) {
        MessageBox.Show(this, "Error" + e.Message);
    } finally {
        conn.Close();
    }
}