#Mysql

foreign.md

forign key requirement

1. Engine should be the same e.g. InnoDB
2. Datatype should be the same, and with same length. e.g. VARCHAR(20) or e.g. Unsigned both
3. Collation Columns charset should be the same. e.g. utf8
4. Watchout: Even if your tables have same Collation, columns still could have different one.
5. Unique - Foreign key should refer to field that is unique (usually primary key) in the reference table.

ON UPDAE: If you use primiary index as a forign key, it doesn't take effect

If parent table update/delete then update/delete child table(including foreign key) ON DELETE CASCADE ON UPDATE CASCADE

group.md

SELECT COUNT(*) FROM articles WHERE name is not NULL and domain is not NULL GROUP BY domain,name

Note

articles 5M with flags heap_max_size,tmp_size still didn't work, HOW TO FIX?? Duplicate entry for key '<group_key>'

It can work if we limit date to small size:) SELECT name,domain,COUNT(*) FROM (SELECT * FROM articles WHERE media_id is NULL LIMIT 10000) AS B WHERE name is not NULL and domain is not NULL GROUP BY name,domain

GROUP order

Indexs.md

Indexs:

Secondary Index: not prmiary key Unique Index: the performace is almost same compared to (Secondary) index

partition.md

1. FK table (where another table refers to) can not partitioning
2. partition key need to be primary and Unique index?

• display information of partition SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=;

• remove partitions not drop data LTER TABLE ... REMOVE PARTITIONING

• Query SELECT .... PARTATIONS (partitions,subpartitions)

• Partition Types

1. RANGE
2. LIST
3. HASH - only for Interger - , N = Mod(int,num)
4. KEY - use md5(), Varchar is valid , N = Mod(MD5(),num) where N is the Nth partition
5. SUBPARTITION

view.md

Materialize view

:directly create table with refresh mechanism

link1 link2 0. ON DEMAND : CALL PROCEDURE

• SHOW PROCEDURE STATUS;

1. Trigger

• SHOW TRIGGERS;

2. event sheduler