SQL Programming (Advance)

Forcing/Ignore MySQL Index

SELECT * FROM t1, t2 FORCE INDEX (index_col1, index_col2)
  WHERE t1.col1=t2.col2;
SELECT * FROM t1, t2 IGNORE INDEX  (index_col1, index_col2)
  WHERE t1.col1=t2.col2;

MySQL User Defined Variable

mysql> SELECT @low_salary:=MIN(salary),@high_salary:=MAX(salary) FROM emp_salary;
mysql> SELECT * FROM emp_salary WHERE salary=@low_salary OR salary=@high_salary;

MySQL Comment

mysql> SELECT 1; # comment
mysql> SELECT 1 + /* in-line comment */
            1;

MySQL Stored Procedure

Define Store Procedure - a Function

# Change the statement delimiter from ; to //
# So ; can be used within the function declaration
# And declare a local variable rate
mysql> DELIMITER //
mysql> CREAYE FUNCTION compute_total (value FLOAT) RETURNS FLOAT
-> BEGIN
-> DECLARE rate FLOAT DEFAULT 1.05;
-> RETURN value*rate;
-> END
-> //
mysql> DELIMITER ;

Calling store procedure

mysql> SELECT compute_total(10.0);

Define Store Procedure - a Procedure

mysql> DELIMITER //
mysql> CREATE PROCEDURE total_bonus (OUT total float, IN emp int)
-> BEGIN
-> SELECT sum(amount) INTO total FROM bonus WHERE employee = emp;
-> END
-> //
mysql> DELIMITER ;
mysql> CALL total_bonus(@total, 1034);
mysql> SELECT @total;

Parameters:

IN : Input parameter

OUT: Output parameter

INOUT: IN and OUT parameter

Control cursor

mysql> DELIMITER //
-> CREATE PROCEDURE highest_bonus(out largest int)
-> begin
->
->  declare done int default 0;
->  declare value float;
->  declare max_amount float default 0.0;
->
->  declare continue handler for sqlstate '02000' set done = 1;
->
->  declare cur cursor for select amount from bonus;
->
->  open cur;
->  repeat
->    fetch cur into value;
->    if not done then
->      if value > max_amount then
->        set max_amount = value;
->      end if;
->    end if;
->   until done end repeat;
->  close cur;
->
->  set largest = max_amount;
->
-> end
-> //
mysql> delimiter ;
mysql> CALL highest_bonus(@max_amount);
mysql> SELECT @max_amount;

Define a cursor for the SQL select amount from bonus

declare cur cursor for select amount from bonus;

Define A SQL handler: When sqlstate 02000 (No more rows) is raised, set done = 1;

declare continue handler for sqlstate '02000' set done = 1;

Fetch the next row and assign it to "value"

fetch cur into value;

Control Structure

if condition then
    statements
    [elseif condition then]
    statements
    [else]
    statements
end if;
case value
    when value then statement
    [when value then statement ...]
    [else statement]
end case;
repeat
statements
until done_value end repeat;
while condition do
statements
end while;
loop
 statements
 if condition then
    leave;
 end if;
 statements
end loop;

Manage Stored Procedure

Show stored procedure

mysql> SHOW CREATE FUNCTION compute_total;
mysql> SHOW CREATE PROCEDURE total_bonus;

Delete stored procedure

mysql> DELETE CREATE FUNCTION compute_total;
mysql> DELETE CREATE PROCEDURE total_bonus;

MySQL Triggers

Syntax

mysql> CREATE TRIGGER tbl_sum trigger_name trigger_time trigger_event ON tbl
 FOR EACH ROW SET statement;

trigger_time: BEFORE | AFTER
trigger_event: INSERT | UPDATE |DELETE

Sample

mysql> SET @sum = 0;
mysql> CREATE TABLE tbl (id INT, value INT);
mysql> delimiter //
mysql> CREATE TRIGGER add BEFORE INSERT ON tbl
        FOR EACH ROW
          BEGIN
            SET @sum = @sum + NEW.value;
            ...
          END;
mysql> delimiter ;
mysql> INSERT INTO tbl VALUES(1,10),(2.13));
mysql> SELECT @sum;

Drop a trigger

DROP TRIGGER tbl.add

Show triggers

SHOW TRIGGERS LIKE 'tbl%'\G

MySQL Event scheduler

To enable/disable event scheduler

SET GLOBAL event_scheduler = ON;

SET GLOBAL event_scheduler = OFF;

To verify

mysql> SHOW PROCESSLIST\G

The event_scheduler should be shown if it is ON

mysql> CREATE EVENT evt
   ON SCHEDULE AT '2009-10-10 01:00:00'
   DO INSERT INTO my_db.tbl VALUES (1);
mysql> CREATE EVENT evt
   ON SCHEDULE EVERY 10 HOUR
   DO INSERT INTO my_db.tbl VALUES (1);

Show events

mysql> SHOW EVENTS LIKE '%evt%';

Drop event

mysql> DROP EVENT evt;

MySQL Views

Create view

mysql> CREATE VIEW v1 AS SELECT first_name AS name FROM tbl;

Use view

mysql> SELECT * from v1;
mysql> UPDATE v1 SET col1 = 0;

Show view

SHOW CREATE VIEW v1;

Drop view

DROP VIEW v1;

MySQL SQL Best Practices

  • Prefer joining table over the use of subquery
  • Use Limit to reduce the returned rows if not all of them is needed
    mysql> select * from tbl LIMIT 50;
    
  • Avoid using MyISAM for tables require frequent read and write
  • Use a multiple-row INSERT rather than multiple INSERTS
    mysql> INSERT INTO tbl (col1,col2,col3) VALUES(0,0,0),(1,1,1),(2,2,2);
    
  • Use LOAD DATA INFILE (Instead of INSERT) to load large amounts of data
  • Use AUTO INCREMENT to create a unique/primary column (if no primary/unique key exists yet)
  • Use MEMORY table for volatile data access
  • Use client side's connection pool to avoid overhead in establishing new connection
  • Reduce buffer flushing for multiples INSERT, UPDATE, and DELETE
    • For non-transactional table, lock the table before multiple SQL statements if there are no negative impacts on other session. This improves performance by flushing buffer only once when table is un-locked.
      mysql> LOCK TABLES tbl WRITE;
      mysql> INSERT INTO tbl VALUES (0,0),(1,1),(2,2);
      mysql> INSERT INTO tbl VALUES (4,4),(5,5);
      ...
      mysql> UNLOCK TABLES;
      
    • For transactional table, wrap the statements with START TRANSACTION & COMMIT
  • If no rows are deleted from a table or after OPTIMIZE TABLE, MyISAM engine allow concurrent read and INSERT.
  • To insert huge amount of data to a table with many indexes, disable the index temporarily
  • SELECT HIGH_PRIORITY for SQLs that demand much faster response time
  • INSERT LOW_PRIORITY give SELECT statements higher priority and hence may improve read performance
  • INSERT DELAYED may flush multiple changes in less file I/O and hence improve system performance
  • Table with all fix size column (e.g. no VARCHAR type) has better performance
  • Use DROP/CREATE tbl instead of DELETE to delete all the data in a table (if possible)