I'm creating a table structure which needs to handle a variable number of columns. To handle that case the table structure is a listing of cells with a column and row number. To view the table a SELECT statement is used with a JOIN of the appropriate cells table for every new column.
My coworkers have started putting data into this to test it out and are at about 10 million rows. The below SQL query takes about 5 or 6 seconds but the result is only about 22,000 rows, so this seems slow.
Does anyone have insight on how to make better use of indexes for this case? My intuition is that since there's only one multiple column index of (table_id, column, row) and I always provide constants for table_id and column, then row should always be indexed in this query.
DESC cells_text:
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| table_id | int(11) | NO | MUL | NULL | |
| row | int(11) | NO | | NULL | |
| column | int(11) | NO | | NULL | |
| data | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
DESC cells_float:
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| table_id | int(11) | NO | MUL | NULL | |
| row | int(11) | NO | | NULL | |
| column | int(11) | NO | | NULL | |
| data | float | YES | | NULL | |
+----------+---------+------+-----+---------+-------+
SHOW INDEX FROM cells_text
+----------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| cells_text | 1 | idx_cells_text2 | 1 | table_id | A | 17 | NULL | NULL | | BTREE | |
| cells_text | 1 | idx_cells_text2 | 2 | column | A | 814 | NULL | NULL | | BTREE | |
| cells_text | 1 | idx_cells_text2 | 3 | row | A | 1061000 | NULL | NULL | | BTREE | |
+----------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM cells_float:
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| cells_float | 1 | idx_6 | 1 | table_id | A | 17 | NULL | NULL | | BTREE | |
| cells_float | 1 | idx_6 | 2 | column | A | 17 | NULL | NULL | | BTREE | |
| cells_float | 1 | idx_6 | 3 | row | A | 10241787 | NULL | NULL | | BTREE | |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SQL:
SELECT SQL_NO_CACHE
`c_0`.`row` AS row_num,
c_0.data AS `c_0_data`,
c_1.data AS `c_1_data`,
c_2.data AS `c_2_data`,
c_3.data AS `c_3_data`,
c_4.data AS `c_4_data`,
c_5.data AS `c_5_data`,
c_6.data AS `c_6_data`,
c_7.data AS `c_7_data`,
c_8.data AS `c_8_data`,
c_9.data AS `c_9_data`,
c_10.data AS `c_10_data`,
c_11.data AS `c_11_data`,
c_12.data AS `c_12_data`,
c_13.data AS `c_13_data`,
c_14.data AS `c_14_data`,
c_15.data AS `c_15_data`,
c_16.data AS `c_16_data`,
c_17.data AS `c_17_data`,
c_18.data AS `c_18_data`,
c_19.data AS `c_19_data`,
c_20.data AS `c_20_data`,
c_21.data AS `c_21_data`,
c_22.data AS `c_22_data`,
c_23.data AS `c_23_data`,
c_24.data AS `c_24_data`,
c_25.data AS `c_25_data`,
c_26.data AS `c_26_data`,
c_27.data AS `c_27_data`,
c_28.data AS `c_28_data`,
c_29.data AS `c_29_data`,
c_30.data AS `c_30_data`,
c_31.data AS `c_31_data`,
c_32.data AS `c_32_data`,
c_33.data AS `c_33_data`,
c_34.data AS `c_34_data`,
c_35.data AS `c_35_data`,
c_36.data AS `c_36_data`,
c_37.data AS `c_37_data`,
c_38.data AS `c_38_data`,
c_39.data AS `c_39_data`,
c_40.data AS `c_40_data`,
c_41.data AS `c_41_data`,
c_42.data AS `c_42_data`,
c_43.data AS `c_43_data`,
c_44.data AS `c_44_data`,
c_45.data AS `c_45_data`,
c_46.data AS `c_46_data`,
c_47.data AS `c_47_data`,
c_48.data AS `c_48_data`,
c_49.data AS `c_49_data`,
c_50.data AS `c_50_data`,
c_51.data AS `c_51_data`,
c_52.data AS `c_52_data`,
c_53.data AS `c_53_data`,
c_54.data AS `c_54_data`,
c_55.data AS `c_55_data`,
c_56.data AS `c_56_data`
FROM cells_text AS c_0 JOIN cells_text AS c_1
ON c_0.row = c_1.row AND c_1.table_id = 28 AND c_1.column = 1
JOIN cells_text AS c_2 ON c_0.row = c_2.row AND c_2.table_id = 28 AND c_2.column = 2
JOIN cells_text AS c_3 ON c_0.row = c_3.row AND c_3.table_id = 28 AND c_3.column = 3
JOIN cells_float AS c_4 ON c_0.row = c_4.row AND c_4.table_id = 28 AND c_4.column = 4
JOIN cells_float AS c_5 ON c_0.row = c_5.row AND c_5.table_id = 28 AND c_5.column = 5
JOIN cells_float AS c_6 ON c_0.row = c_6.row AND c_6.table_id = 28 AND c_6.column = 6
JOIN cells_float AS c_7 ON c_0.row = c_7.row AND c_7.table_id = 28 AND c_7.column = 7
JOIN cells_float AS c_8 ON c_0.row = c_8.row AND c_8.table_id = 28 AND c_8.column = 8
JOIN cells_float AS c_9 ON c_0.row = c_9.row AND c_9.table_id = 28 AND c_9.column = 9
JOIN cells_float AS c_10 ON c_0.row = c_10.row AND c_10.table_id = 28 AND c_10.column = 10
JOIN cells_float AS c_11 ON c_0.row = c_11.row AND c_11.table_id = 28 AND c_11.column = 11
JOIN cells_float AS c_12 ON c_0.row = c_12.row AND c_12.table_id = 28 AND c_12.column = 12
JOIN cells_float AS c_13 ON c_0.row = c_13.row AND c_13.table_id = 28 AND c_13.column = 13
JOIN cells_float AS c_14 ON c_0.row = c_14.row AND c_14.table_id = 28 AND c_14.column = 14
JOIN cells_float AS c_15 ON c_0.row = c_15.row AND c_15.table_id = 28 AND c_15.column = 15
JOIN cells_float AS c_16 ON c_0.row = c_16.row AND c_16.table_id = 28 AND c_16.column = 16
JOIN cells_float AS c_17 ON c_0.row = c_17.row AND c_17.table_id = 28 AND c_17.column = 17
JOIN cells_float AS c_18 ON c_0.row = c_18.row AND c_18.table_id = 28 AND c_18.column = 18
JOIN cells_float AS c_19 ON c_0.row = c_19.row AND c_19.table_id = 28 AND c_19.column = 19
JOIN cells_float AS c_20 ON c_0.row = c_20.row AND c_20.table_id = 28 AND c_20.column = 20
JOIN cells_float AS c_21 ON c_0.row = c_21.row AND c_21.table_id = 28 AND c_21.column = 21
JOIN cells_float AS c_22 ON c_0.row = c_22.row AND c_22.table_id = 28 AND c_22.column = 22
JOIN cells_float AS c_23 ON c_0.row = c_23.row AND c_23.table_id = 28 AND c_23.column = 23
JOIN cells_float AS c_24 ON c_0.row = c_24.row AND c_24.table_id = 28 AND c_24.column = 24
JOIN cells_float AS c_25 ON c_0.row = c_25.row AND c_25.table_id = 28 AND c_25.column = 25
JOIN cells_float AS c_26 ON c_0.row = c_26.row AND c_26.table_id = 28 AND c_26.column = 26
JOIN cells_float AS c_27 ON c_0.row = c_27.row AND c_27.table_id = 28 AND c_27.column = 27
JOIN cells_float AS c_28 ON c_0.row = c_28.row AND c_28.table_id = 28 AND c_28.column = 28
JOIN cells_float AS c_29 ON c_0.row = c_29.row AND c_29.table_id = 28 AND c_29.column = 29
JOIN cells_float AS c_30 ON c_0.row = c_30.row AND c_30.table_id = 28 AND c_30.column = 30
JOIN cells_float AS c_31 ON c_0.row = c_31.row AND c_31.table_id = 28 AND c_31.column = 31
JOIN cells_float AS c_32 ON c_0.row = c_32.row AND c_32.table_id = 28 AND c_32.column = 32
JOIN cells_float AS c_33 ON c_0.row = c_33.row AND c_33.table_id = 28 AND c_33.column = 33
JOIN cells_float AS c_34 ON c_0.row = c_34.row AND c_34.table_id = 28 AND c_34.column = 34
JOIN cells_float AS c_35 ON c_0.row = c_35.row AND c_35.table_id = 28 AND c_35.column = 35
JOIN cells_float AS c_36 ON c_0.row = c_36.row AND c_36.table_id = 28 AND c_36.column = 36
JOIN cells_float AS c_37 ON c_0.row = c_37.row AND c_37.table_id = 28 AND c_37.column = 37
JOIN cells_float AS c_38 ON c_0.row = c_38.row AND c_38.table_id = 28 AND c_38.column = 38
JOIN cells_float AS c_39 ON c_0.row = c_39.row AND c_39.table_id = 28 AND c_39.column = 39
JOIN cells_float AS c_40 ON c_0.row = c_40.row AND c_40.table_id = 28 AND c_40.column = 40
JOIN cells_float AS c_41 ON c_0.row = c_41.row AND c_41.table_id = 28 AND c_41.column = 41
JOIN cells_float AS c_42 ON c_0.row = c_42.row AND c_42.table_id = 28 AND c_42.column = 42
JOIN cells_float AS c_43 ON c_0.row = c_43.row AND c_43.table_id = 28 AND c_43.column = 43
JOIN cells_float AS c_44 ON c_0.row = c_44.row AND c_44.table_id = 28 AND c_44.column = 44
JOIN cells_float AS c_45 ON c_0.row = c_45.row AND c_45.table_id = 28 AND c_45.column = 45
JOIN cells_float AS c_46 ON c_0.row = c_46.row AND c_46.table_id = 28 AND c_46.column = 46
JOIN cells_float AS c_47 ON c_0.row = c_47.row AND c_47.table_id = 28 AND c_47.column = 47
JOIN cells_float AS c_48 ON c_0.row = c_48.row AND c_48.table_id = 28 AND c_48.column = 48
JOIN cells_float AS c_49 ON c_0.row = c_49.row AND c_49.table_id = 28 AND c_49.column = 49
JOIN cells_float AS c_50 ON c_0.row = c_50.row AND c_50.table_id = 28 AND c_50.column = 50
JOIN cells_float AS c_51 ON c_0.row = c_51.row AND c_51.table_id = 28 AND c_51.column = 51
JOIN cells_float AS c_52 ON c_0.row = c_52.row AND c_52.table_id = 28 AND c_52.column = 52
JOIN cells_float AS c_53 ON c_0.row = c_53.row AND c_53.table_id = 28 AND c_53.column = 53
JOIN cells_float AS c_54 ON c_0.row = c_54.row AND c_54.table_id = 28 AND c_54.column = 54
JOIN cells_float AS c_55 ON c_0.row = c_55.row AND c_55.table_id = 28 AND c_55.column = 55
JOIN cells_float AS c_56 ON c_0.row = c_56.row AND c_56.table_id = 28 AND c_56.column = 56
WHERE c_0.`column` = 0 AND c_0.`table_id` = 28
ORDER BY c_0.row;
EXPLAIN:
+----+-------------+-------+------+-----------------+-----------------+---------+-----------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+-----------------------------+-------+---------------------------------+
| 1 | SIMPLE | c_5 | ref | idx_6 | idx_6 | 8 | const,const | 13794 | Using temporary; Using filesort |
| 1 | SIMPLE | c_49 | ref | idx_6 | idx_6 | 12 | const,const,db.c_5.row | 1 | |
| 1 | SIMPLE | c_26 | ref | idx_6 | idx_6 | 12 | const,const,db.c_49.row | 1 | Using where |
| 1 | SIMPLE | c_52 | ref | idx_6 | idx_6 | 12 | const,const,db.c_5.row | 1 | |
| 1 | SIMPLE | c_21 | ref | idx_6 | idx_6 | 12 | const,const,db.c_26.row | 1 | Using where |
| 1 | SIMPLE | c_25 | ref | idx_6 | idx_6 | 12 | const,const,db.c_52.row | 1 | Using where |
| 1 | SIMPLE | c_48 | ref | idx_6 | idx_6 | 12 | const,const,db.c_52.row | 1 | Using where |
| 1 | SIMPLE | c_16 | ref | idx_6 | idx_6 | 12 | const,const,db.c_21.row | 1 | Using where |
| 1 | SIMPLE | c_20 | ref | idx_6 | idx_6 | 12 | const,const,db.c_26.row | 1 | Using where |
| 1 | SIMPLE | c_29 | ref | idx_6 | idx_6 | 12 | const,const,db.c_52.row | 1 | Using where |
| 1 | SIMPLE | c_15 | ref | idx_6 | idx_6 | 12 | const,const,db.c_29.row | 1 | Using where |
| 1 | SIMPLE | c_51 | ref | idx_6 | idx_6 | 12 | const,const,db.c_29.row | 1 | Using where |
| 1 | SIMPLE | c_32 | ref | idx_6 | idx_6 | 12 | const,const,db.c_52.row | 1 | Using where |
| 1 | SIMPLE | c_7 | ref | idx_6 | idx_6 | 12 | const,const,db.c_5.row | 1 | |
| 1 | SIMPLE | c_53 | ref | idx_6 | idx_6 | 12 | const,const,db.c_16.row | 1 | Using where |
| 1 | SIMPLE | c_35 | ref | idx_6 | idx_6 | 12 | const,const,db.c_48.row | 1 | Using where |
| 1 | SIMPLE | c_38 | ref | idx_6 | idx_6 | 12 | const,const,db.c_48.row | 1 | Using where |
| 1 | SIMPLE | c_44 | ref | idx_6 | idx_6 | 12 | const,const,db.c_51.row | 1 | Using where |
| 1 | SIMPLE | c_28 | ref | idx_6 | idx_6 | 12 | const,const,db.c_16.row | 1 | Using where |
| 1 | SIMPLE | c_41 | ref | idx_6 | idx_6 | 12 | const,const,db.c_49.row | 1 | Using where |
| 1 | SIMPLE | c_50 | ref | idx_6 | idx_6 | 12 | const,const,db.c_53.row | 1 | Using where |
| 1 | SIMPLE | c_0 | ref | idx_cells_text2 | idx_cells_text2 | 12 | const,const,db.c_16.row | 1 | Using where |
| 1 | SIMPLE | c_4 | ref | idx_6 | idx_6 | 12 | const,const,db.c_41.row | 1 | Using where |
| 1 | SIMPLE | c_31 | ref | idx_6 | idx_6 | 12 | const,const,db.c_44.row | 1 | Using where |
| 1 | SIMPLE | c_27 | ref | idx_6 | idx_6 | 12 | const,const,db.c_41.row | 1 | Using where |
| 1 | SIMPLE | c_54 | ref | idx_6 | idx_6 | 12 | const,const,db.c_26.row | 1 | Using where |
| 1 | SIMPLE | c_1 | ref | idx_cells_text2 | idx_cells_text2 | 12 | const,const,db.c_21.row | 1 | Using where |
| 1 | SIMPLE | c_24 | ref | idx_6 | idx_6 | 12 | const,const,db.c_35.row | 1 | Using where |
| 1 | SIMPLE | c_8 | ref | idx_6 | idx_6 | 12 | const,const,db.c_15.row | 1 | Using where |
| 1 | SIMPLE | c_34 | ref | idx_6 | idx_6 | 12 | const,const,db.c_44.row | 1 | Using where |
| 1 | SIMPLE | c_47 | ref | idx_6 | idx_6 | 12 | const,const,db.c_50.row | 1 | Using where |
| 1 | SIMPLE | c_6 | ref | idx_6 | idx_6 | 12 | const,const,db.c_51.row | 1 | Using where |
| 1 | SIMPLE | c_37 | ref | idx_6 | idx_6 | 12 | const,const,db.c_47.row | 1 | Using where |
| 1 | SIMPLE | c_19 | ref | idx_6 | idx_6 | 12 | const,const,db.c_21.row | 1 | Using where |
| 1 | SIMPLE | c_40 | ref | idx_6 | idx_6 | 12 | const,const,db.c_48.row | 1 | Using where |
| 1 | SIMPLE | c_43 | ref | idx_6 | idx_6 | 12 | const,const,db.c_51.row | 1 | Using where |
| 1 | SIMPLE | c_33 | ref | idx_6 | idx_6 | 12 | const,const,db.c_44.row | 1 | Using where |
| 1 | SIMPLE | c_22 | ref | idx_6 | idx_6 | 12 | const,const,db.c_34.row | 1 | Using where |
| 1 | SIMPLE | c_30 | ref | idx_6 | idx_6 | 12 | const,const,db.c_43.row | 1 | Using where |
| 1 | SIMPLE | c_3 | ref | idx_cells_text2 | idx_cells_text2 | 12 | const,const,db.c_28.row | 1 | Using where |
| 1 | SIMPLE | c_2 | ref | idx_cells_text2 | idx_cells_text2 | 12 | const,const,db.c_44.row | 1 | Using where |
| 1 | SIMPLE | c_56 | ref | idx_6 | idx_6 | 12 | const,const,db.c_41.row | 1 | Using where |
| 1 | SIMPLE | c_17 | ref | idx_6 | idx_6 | 12 | const,const,db.c_29.row | 1 | Using where |
| 1 | SIMPLE | c_14 | ref | idx_6 | idx_6 | 12 | const,const,db.c_28.row | 1 | Using where |
| 1 | SIMPLE | c_45 | ref | idx_6 | idx_6 | 12 | const,const,db.c_48.row | 1 | Using where |
| 1 | SIMPLE | c_55 | ref | idx_6 | idx_6 | 12 | const,const,db.c_28.row | 1 | Using where |
| 1 | SIMPLE | c_36 | ref | idx_6 | idx_6 | 12 | const,const,db.c_45.row | 1 | Using where |
| 1 | SIMPLE | c_11 | ref | idx_6 | idx_6 | 12 | const,const,db.c_51.row | 1 | Using where |
| 1 | SIMPLE | c_12 | ref | idx_6 | idx_6 | 12 | const,const,db.c_26.row | 1 | Using where |
| 1 | SIMPLE | c_9 | ref | idx_6 | idx_6 | 12 | const,const,db.c_54.row | 1 | Using where |
| 1 | SIMPLE | c_39 | ref | idx_6 | idx_6 | 12 | const,const,db.c_43.row | 1 | Using where |
| 1 | SIMPLE | c_10 | ref | idx_6 | idx_6 | 12 | const,const,db.c_33.row | 1 | Using where |
| 1 | SIMPLE | c_42 | ref | idx_6 | idx_6 | 12 | const,const,db.c_51.row | 1 | Using where |
| 1 | SIMPLE | c_23 | ref | idx_6 | idx_6 | 12 | const,const,db.c_35.row | 1 | Using where |
| 1 | SIMPLE | c_18 | ref | idx_6 | idx_6 | 12 | const,const,db.c_21.row | 1 | Using where |
| 1 | SIMPLE | c_46 | ref | idx_6 | idx_6 | 12 | const,const,db.c_50.row | 1 | Using where |
| 1 | SIMPLE | c_13 | ref | idx_6 | idx_6 | 12 | const,const,db.c_46.row | 1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+-----------------------------+-------+---------------------------------+
SHOW CREATE TABLE cells_text
CREATE TABLE `cells_text` (
`table_id` int(11) NOT NULL,
`row` int(11) NOT NULL,
`column` int(11) NOT NULL,
`data` varchar(255) default NULL,
KEY `idx_cells_text2` (`table_id`,`column`,`row`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE cells_float
CREATE TABLE `cells_float` (
`table_id` int(11) NOT NULL,
`row` int(11) NOT NULL,
`column` int(11) NOT NULL,
`data` float default NULL,
PRIMARY KEY (`table_id`,`column`,`row`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
EXPLAIN with only cells_text columns
mysql> explain SELECT SQL_NO_CACHE `c_0`.`row` AS row_num, c_0.data AS `c_0_data`, c_1.data AS `c_1_data`, c_2.data AS `c_2_data`, c_3.data AS `c_3_data` FROM cells_text AS c_0 JOIN cells_text AS c_1 ON c_0.row = c_1.row AND c_1.table_id = 28 AND c_1.column = 1 JOIN cells_text AS c_2 ON c_0.row = c_2.row AND c_2.table_id = 28 AND c_2.column = 2 JOIN cells_text AS c_3 ON c_0.row = c_3.row AND c_3.table_id = 28 AND c_3.column = 3 WHERE c_0.`column` = 0 AND c_0.`table_id` = 28 ORDER BY c_0.row;
+----+-------------+-------+------+-----------------+-----------------+---------+----------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+----------------------------+-------+-------------+
| 1 | SIMPLE | c_0 | ref | idx_cells_text2 | idx_cells_text2 | 8 | const,const | 21472 | Using where |
| 1 | SIMPLE | c_1 | ref | idx_cells_text2 | idx_cells_text2 | 12 | const,const,db.c_0.row | 1 | |
| 1 | SIMPLE | c_3 | ref | idx_cells_text2 | idx_cells_text2 | 12 | const,const,db.c_0.row | 1 | |
| 1 | SIMPLE | c_2 | ref | idx_cells_text2 | idx_cells_text2 | 12 | const,const,db.c_1.row | 1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+----------------------------+-------+-------------+
Best Answer
I found that MySQL has string extraction functions like ExtractValue and SUBSTRING_INDEX which work inside WHERE and ORDER BY clauses. So I replaced the cells tables with a rows table which contains a tab delimited string, and I use SUBSTRING_INDEX and CAST to get SQL access to the individual cells.