select query on a table with many rows (250m+ rows)

Question

I have a select statement that takes a while (a very long time) for MySQL to complete. All tables are "MyISAM". MySQL version is 5.6.41.

I have three tables

table 1: 50,000 rows
table 2: 7,500,000 rows (7.5GB)
table 3: 250,000,000 rows (17.5GB)

I perform a select statement with the left joins on the table.

select `table1`.* 
from `table1`  
inner join `table2` on `table2`.`table1_id` = `table1`.`id` 
inner join `table3` on `table3`.`table2_id` = `table2`.`id` 
where `table1`.`id` = "2" and `table3`.`parameter` = 'param'

"id" columns are primary keys and "parameter" column has index applied on it

Current table code

CREATE TABLE `table1` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (`id`), 
    INDEX `id` (`id`), 
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM;

CREATE TABLE `table2` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    `table1_id` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`), 
    INDEX `id` (`id`), 
    INDEX `table1_id_index` (`table1_id`),
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM;


CREATE TABLE `table3` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    `table2_id` INT(10) UNSIGNED NOT NULL,
    `parameter` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`id`), 
    INDEX `id` (`id`), 
    INDEX `id` (`parameter`), 
    INDEX `table2_id_index` (`table1_id`),
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM;

The odd thing is that when i re-write the query other way round i have no performance issues:

select `table1`.* 
from `table3`  
inner join `table2` on `table3`.`table2_id` = `table2`.`id` 
inner join `table1` on `table2`.`table1_id` = `table1`.`id` 
where `table1`.`id` = "2" and `table3`.`parameter` = 'param'

Are you asking how you might speed this up? If so, do you have indexes for any of these tables yet? If not, adding an index on each table for the `id` field would probably help a ton here. — JNevill, Nov 29 '18 at 17:36
Well. do you have the proper indexes applied, and are they getting used? — OldProgrammer, Nov 29 '18 at 17:37
As for the `like '%param%'` portion perhaps look into [`Full-Text Search`](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html) and a `FULLTEXT` index. — JNevill, Nov 29 '18 at 17:39
Do you really want to get all the columns from `table1`. Generally, it is not the case. Consider fetching only those columns which you really need in your application. Check: [Why is SELECT * considered harmful?](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) — Madhur Bhaiya, Nov 29 '18 at 17:46
If your problem is low speed of the query, then create this index on table3: `CREATE INDEX my_fast_index ON table3( table2_id, parameter )` — krokodilko, Nov 29 '18 at 17:49
`KEY(id)` is totally redundant with `PRIMARY KEY(id)`. Keep the PK; drop the other. — Rick James, Nov 29 '18 at 23:15

score 0 · Answer 1 · answered Nov 29 '18 at 17:53

0

be sure you have proper index for id and be sure you don't perform unuseful data conversion and for

create index  idx1 on table2 (table1_id, id) 
create index  idx2 on table3 (table2_id, parameter)

answered Nov 29 '18 at 17:53

ScaisEdge

131,976
10
91
107

indexing parameter won't help in this instance :-( – Strawberry Nov 29 '18 at 18:05
what would help? – Vlad Vladimir Hercules Nov 29 '18 at 18:06
@user3402600 the indices shown are more efficient than the individual indices – ScaisEdge Nov 29 '18 at 19:27
@Strawberry - `(table2_id, param)` is at least "covering", thereby avoiding a disk seek for _every_ lookup. Flipping the order optimizes it for starting with `table3`. – Rick James Nov 29 '18 at 23:19

score 0 · Answer 2 · answered Nov 29 '18 at 23:17

0

Switch to InnoDB. It "clusters" the PRIMARY KEY with the data, thereby avoiding several random seeks that you are suffering from with MyISAM.

Meanwhile, what is the value of key_buffer_size? How much RAM do you have?

answered Nov 29 '18 at 23:17

Rick James

135,179
13
127
222

select query on a table with many rows (250m+ rows)

2 Answers2