by Markus Winand.

MySQL Example Scripts for “Clustering Data”


This section contains the create and insert code to run the examples from Chapter 5, “Clustering Data: The Second Power of Indexing in an MySQL database.

Index-Organized Tables (Clustered Indexes)

The following creates a second SALES Table using the InnoDB engine so that it is created as clustered index. A secondary index is added on the SALE_DATE column.

CREATE TABLE sales_inno (
  sale_id       NUMERIC NOT NULL,
  employee_id   NUMERIC NOT NULL,
  subsidiary_id NUMERIC NOT NULL,
  sale_date     DATE   NOT NULL,
  eur_value     NUMERIC(17,2) NOT NULL,
  junk          CHAR(200),
  CONSTRAINT sales_pk     
     PRIMARY KEY (sale_id)
) Engine=InnoDB;

INSERT INTO sales_inno (sale_id
                      , subsidiary_id, employee_id
                      , sale_date, eur_value, junk)
SELECT @row := @row + 1 sale_id
     , data.*
  FROM (
       SELECT e.subsidiary_id, e.employee_id
            , CURDATE() - INTERVAL (RAND(0)*3650) DAY sale_date
            , TRUNCATE(RAND(1)*99.90+0.1,2) eur_value
            , 'junk'
         FROM employees e
            , ( SELECT generator_4k.n+1 n
                  FROM generator_4k
                 WHERE generator_4k.n < 1800
              ) gen
        WHERE MOD(employee_id, 7) = 4
          AND gen.n < employee_id / 5
        ORDER BY sale_date
       ) data, (SELECT @row := 0) init
  WHERE DAYOFWEEK(sale_date) NOT IN (1,7);


CREATE INDEX sales_inno_dt ON sales_inno (sale_date);

Note the “Using Index” indicating an Index-Only Scan:

EXPLAIN
 SELECT sale_id 
   FROM sales_inno
  WHERE sale_date = ?;

+----+------------+------+---------------+------+-------------+
| id | table      | type | key           | rows | Extra       |
+----+------------+------+---------------+------+-------------+
|  1 | sales_inno | ref  | sales_inno_dt |  301 | Using index |
+----+------------+------+---------------+------+-------------+

Previous pageNext page

You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Bluesky or RSS to gradually catch up. Have a look at modern-⁠sql.com as well.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Connect with Markus Winand

Subscribe mailinglistsSubscribe the RSS feedMarkus Winand on LinkedInMarkus Winand on XINGMarkus Winand on TwitterMarkus Winand on Bluesky
Copyright 2010-2025 Markus Winand. All righs reserved.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR