The TransEvents Table

DDL

CREATE TABLE IF NOT EXISTS `sb_trans_events`
(
Rowid MEDIUMINT NOT NULL AUTO_INCREMENT,
Trans_ID BIGINT(20) NOT NULL, 
Trans_date DATE NOT NULL,
Trans_time TIME NOT NULL,
Trans_amt DECIMAL(13,2) NOT NULL, 
Matched_ind CHAR(2) NOT NULL DEFAULT 'DK',
Event_date DATE NOT NULL,
Event_time TIME NOT NULL,
Event_lag TIME DEFAULT NULL,
Event_desc VARCHAR(500) NOT NULL, CONSTRAINT trev_pk PRIMARY KEY (Rowid)
) WITH SYSTEM VERSIONING ENGINE = MYISAM;

CREATE INDEX sb_trans_idx ON sb_trans_events (Trans_ID);

// run AFTER filload

ALTER TABLE sb_trans_events ADD FULLTEXT KEY sb_event_idx(Event_desc);

LOADFIL DML
(this creates the initial data set for the table by extracting order info from WC)


SELECT 0, p.ID, p.post_date, p.post_date, o.meta_value, '', c.comment_date, c.comment_date, 0, c.comment_content
INTO OUTFILE 'sb_outfile.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'
FROM wp_posts p
LEFT JOIN wp_comments c ON
( p.post_type = "shop_order"
AND p.ID = comment_post_ID
AND c.comment_type = "order_note"
AND c.comment_content NOT LIKE "Item%" // junk 
AND c.comment_content NOT LIKE "Stock%" ) // junk
INNER JOIN wp_postmeta o ON
( o.meta_key = "_order_total"
AND p.ID = o.post_id )
INNER JOIN wp_postmeta x3 ON
( x3.meta_key = "_completed_date"
AND p.ID = x3.post_id )
ORDER BY p.post_date;

COMMENTS

  1. Annoying that PHPMYADMIN does not consider Rowid to be unique
  2. is ORDER BY post_date superfluous?
  3.  DML needs to be explained for possible optimization
Advertisements