robrich

-- FULL TEXT SEARCH
-- ================

-- setup schema
CREATE DATABASE library;

USE library;

CREATE TABLE books (
  title VARCHAR(200) not null,

robrich

license: YOUR_LICENSE_KEY
memsql_server_version: 7.1.8
package_type: rpm
hosts:
- hostname: 127.0.0.1
  localhost: true
  nodes:
  - register: false
    role: Master
    config:

robrich

-- Step_1: setup
Create database testDB;
use testDB;
Create table t( a int, b varchar(70));
insert into t values (1, now(6));
insert into t select a+(select max(a) from t), now(6) from t;
select count(*) from t; 

-- Step_3: updates on primary
insert into t values (-1, now(6));

robrich

CREATE DATABASE IF NOT EXISTS hellomemsql;

USE hellomemsql;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  content varchar(300) NOT NULL,
  createdate TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

robrich

-- Step_1: setup
Create database testDB;
use testDB;
Create table t( a int, b varchar(70));
insert into t values (1, now(6));
insert into t select a+(select max(a) from t), now(6) from t;
select count(*) from t; -- 262144  

-- Step_3: updates on primary
insert into t values (-1, now(6));

robrich

create database acme;

use acme;

create table customer (
  id int,
  name varchar(200),
  properties json not null,
  key (id) using clustered columnstore
);

robrich

create database 'tpc_h';
use 'tpc_h';

CREATE TABLE IF NOT EXISTS `lineitem` (
`l_orderkey` bigint(11) NOT NULL,
`l_partkey` int(11) NOT NULL,
`l_suppkey` int(11) NOT NULL,
`l_linenumber` int(11) NOT NULL,
`l_quantity` decimal(15,2) NOT NULL,
`l_extendedprice` decimal(15,2) NOT NULL,

robrich

-- create a database with 2 master partitions
create database thedb partitions 2;

-- create sample data
use thedb;
create table data (msg varchar(200));
insert into data (msg) values ('the data');

-- notice how we're not using the database efficiently
show partitions;

robrich

-- create a database
create database cosmeticshop;

-- create a table
use cosmeticshop;
create table cosmeticshopfunnel
(
    event_time TIMESTAMP,
    event_type CHAR(18),
    product_id CHAR(10),

robrich

-- MemSQL load-balanced failover
-- =============================

-- Queries demoing load-balanced failover in MemSQL: https://youtu.be/J86fLCMr5dc

create database x_db partitions 8;

select host, port, node_id, availability_group as AG from leaves order by AG;

SELECT master_host as m_host, master_port as m_port, host as repl_host, port as repl_port, GROUP_CONCAT(database_name ORDER BY database_name) AS list_partitions FROM information_schema.mv_cluster_status WHERE metadata_role='slave' GROUP BY master_host, master_port, repl_host, repl_port ORDER BY master_host, master_port, repl_host, repl_port;