All geek questions in one place

Edited-MySQL. Large MyISAM table (40 million records) with an index that is very slow and huge in size on disk

The table contains about 40 million records with:

CREATE TABLE `event` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `some_other_id_not_fk` int(10) unsigned default NOT NULL,
  `event_time` datetime NOT NULL,
  `radius` float default NULL,
  `how_heavy` smallint(6) default NULL,
  PRIMARY KEY  (`id`),
  KEY `event_some_other_id_not_fk` (`some_other_id_not_fk`),
  KEY `event_event_time` (`event_time`)
) ENGINE=MyISAM AUTO_INCREMENT=6506226 DEFAULT CHARSET=utf8

You should know that the column some_other_id_not_fkis small, it contains only 7 different numbers. The real pain is the datetime column event_time, because it contains extremely large numbers of different date-days, and basically everything is allowed: duplicates, as well as unpredictably long intervals without records, to "cover" them. You should also know that pairs ( some_other_id_not_fk, event_time) should be allowed to have duplicates :( I know this causes even more problems: (

I had some experience in optimizing MySQL tables, but such a huge pain never appeared on my horizon: /

"":

  • event_time date1 date2 ( ) .:)
  • , SLOW!!! 30 , : LOAD DATA, DISABLE ENABLE KEYS EXTREMELY ( ), ENABLE.
  • 7 ,

, , .

, - ? timestamp datetime ? , , day, year,... .. ?

+3
5
`id` bigint(20) unsigned NOT NULL auto_increment,

BIGINT? , , INT. 1000 24 , 136 , 32- .

152,5 40 158,8 40 .

`some_other_id_not_fk` int(10) unsigned default NOT NULL,

, 7 . INT? TINYINT? .

114,4 40 some_other_id_not_fk .

`event_time` datetime NOT NULL,

DATETIME? DATETIME 8 , TIMESTAMP 4 . TIMESTAMP, . TIMESTAMP, Y2K38 , .

152,5 40 158,8 40 .

, .

  • : 152.5 + 152.5 + 114.4 = 419.4
  • : 158,8 + 158,8 + ~ 115 = 432,6

: 852

, , . some_other_id_not_fk , . .

, .

, ? , SELECT MyISAM INSERT.

Update

some_other_id_not_fk event_time, (event_time, some_other_id_not_fk). , .

, event_time, event_time, some_other_id_not_fk. some_other_id_not_fk - .

, event_time, some_other_id_not_fk, (event_time, some_other_id_not_fk). (some_other_id_not_fk, event_time).

( ) , , , .

+12

, , , : .

: some_other_id_not_fk (some_other_id_not_fk, event_time). " ", . , event_time, , some_other_id_not_fk.

: , , (event_time, some_other_id_not_fk) event_time, some_other_id_not_fk. , some_other_id_not_fk, event_time, (event_time, some_other_id_not_fk), (some_other_id_not_fk, event_time). . - .

+2

, some_other_id_not_fk ( , 7 , 40 000 000/7). , , 1 (event_time + [] some_other_id_not_fk);

+1

(event_time, some_other_id_not_fk). :

  • 1Gb , 1.2Gb .

  • event event. event_time > STR_TO_DATE ('20091201000000', '% Y% m% d% H% i% s') event. some_other_id_not_fk= 4 | : 353543 : 65.173

  • * event event. event_time > STR_TO_DATE ('20090401000000', '% Y% m% d% H% i% s') event. event_time <= STR_TO_DATE ('20090401010000', '% Y% m% d% H% i% s') event. some_other_id_not_fk (22,4,1,3) | 916 , : 0.030

  • index 350 000 , : insert into event VALUES (...), (...),... | 30 , Yeahaaaaaa:))

  • index disable - insertion - enable enable - 350 000 : insert into event VALUES (...), (...),... | 40 .:) mysql default, , , :)

.

(event_time). , . 1.1Gb. , :

  • | , 30
  • | , 0,1 .

  • -enable insertiton 350 000. ! 35 .

    , , N1.

hobodave, , , (some_other_id_not_fk, event_time) (event_time, some_other_id_not_fk) - ? . some_other_id_not_fk. , IN (x, y,...) some_other_id_not_fk. , .

:

  • ( )
    ...

And I also wonder why such a huge index size requires 1.2Gb per 1Gb of data? Index even more data. My logic tells me that date indexing can be done with a much lower index? I'm right? Is there something related to the type of index, which is probably BTREE?

Thank. You're beautiful. I close the thread.

0
source

Source: https://habr.com/ru/post/1731586/

More articles:


All Articles