optimize indexes?

[beanumber]

The indexes currently take up more than twice as much disk space than the table itself. Is that bad?

bbaumer@bbaumer-Precision-Tower-7810:~/dumps/airlines$ sudo ls -lhS /var/lib/mysql/airlines/ | grep .MYD
-rw-rw---- 1 mysql mysql 336M Aug 31 19:35 flights#P#p21.MYD
-rw-rw---- 1 mysql mysql 320M Sep  1 17:19 flights#P#p20.MYD
-rw-rw---- 1 mysql mysql 319M Aug 31 19:11 flights#P#p19.MYD
-rw-rw---- 1 mysql mysql 318M Sep  1 17:17 flights#P#p18.MYD
-rw-rw---- 1 mysql mysql 317M Aug 31 19:50 flights#P#p22.MYD
-rw-rw---- 1 mysql mysql 292M Aug 31 20:04 flights#P#p23.MYD
-rw-rw---- 1 mysql mysql 292M Aug 31 20:19 flights#P#p24.MYD
-rw-rw---- 1 mysql mysql 289M Sep  1 17:21 flights#P#p27.MYD
-rw-rw---- 1 mysql mysql 288M Aug 31 18:46 flights#P#p17.MYD
-rw-rw---- 1 mysql mysql 277M Aug 31 20:45 flights#P#p26.MYD
-rw-rw---- 1 mysql mysql 276M Aug 31 20:32 flights#P#p25.MYD
-rw-rw---- 1 mysql mysql 266M Aug 31 18:21 flights#P#p15.MYD
-rw-rw---- 1 mysql mysql 264M Sep  1 17:22 flights#P#p28.MYD
-rw-rw---- 1 mysql mysql 252M Aug 31 18:08 flights#P#p14.MYD
-rw-rw---- 1 mysql mysql 245M Aug 31 17:56 flights#P#p13.MYD
-rw-rw---- 1 mysql mysql 240M Aug 31 17:33 flights#P#p11.MYD
-rw-rw---- 1 mysql mysql 238M Aug 31 17:45 flights#P#p12.MYD
-rw-rw---- 1 mysql mysql 237M Sep  1 17:15 flights#P#p10.MYD
-rw-rw---- 1 mysql mysql 236M Aug 31 17:17 flights#P#p9.MYD
-rw-rw---- 1 mysql mysql 235M Aug 31 18:32 flights#P#p16.MYD
-rw-rw---- 1 mysql mysql 195M Aug 31 16:25 flights#P#p4.MYD
-rw-rw---- 1 mysql mysql 192M Aug 31 17:06 flights#P#p8.MYD
-rw-rw---- 1 mysql mysql 190M Aug 31 16:05 flights#P#p2.MYD
-rw-rw---- 1 mysql mysql 188M Aug 31 16:46 flights#P#p6.MYD
-rw-rw---- 1 mysql mysql 188M Aug 31 16:55 flights#P#p7.MYD
-rw-rw---- 1 mysql mysql 188M Aug 31 16:36 flights#P#p5.MYD
-rw-rw---- 1 mysql mysql 185M Aug 31 16:15 flights#P#p3.MYD
-rw-rw---- 1 mysql mysql 131M Sep  1 09:05 flights#P#p29.MYD
-rw-rw---- 1 mysql mysql  48M Aug 31 15:35 flights#P#p1.MYD
-rw-rw---- 1 mysql mysql 2.7M Aug 31 15:34 weather.MYD
-rw-rw---- 1 mysql mysql 272K Aug 31 15:34 planes.MYD
-rw-rw---- 1 mysql mysql 106K Aug 31 15:34 airports.MYD
-rw-rw---- 1 mysql mysql  46K Aug 31 15:34 carriers.MYD
bbaumer@bbaumer-Precision-Tower-7810:~/dumps/airlines$ sudo ls -lhS /var/lib/mysql/airlines/ | grep .MYI
-rw-rw---- 1 mysql mysql 852M Sep  1 17:20 flights#P#p21.MYI
-rw-rw---- 1 mysql mysql 809M Sep  1 17:19 flights#P#p19.MYI
-rw-rw---- 1 mysql mysql 805M Sep  1 17:20 flights#P#p22.MYI
-rw-rw---- 1 mysql mysql 739M Sep  1 17:21 flights#P#p23.MYI
-rw-rw---- 1 mysql mysql 739M Sep  1 17:21 flights#P#p24.MYI
-rw-rw---- 1 mysql mysql 732M Sep  1 17:17 flights#P#p17.MYI
-rw-rw---- 1 mysql mysql 699M Sep  1 17:21 flights#P#p26.MYI
-rw-rw---- 1 mysql mysql 698M Sep  1 17:21 flights#P#p25.MYI
-rw-rw---- 1 mysql mysql 679M Sep  1 17:17 flights#P#p15.MYI
-rw-rw---- 1 mysql mysql 639M Sep  1 17:16 flights#P#p14.MYI
-rw-rw---- 1 mysql mysql 622M Sep  1 17:16 flights#P#p13.MYI
-rw-rw---- 1 mysql mysql 611M Sep  1 17:16 flights#P#p11.MYI
-rw-rw---- 1 mysql mysql 607M Sep  1 17:16 flights#P#p12.MYI
-rw-rw---- 1 mysql mysql 602M Sep  1 17:15 flights#P#p9.MYI
-rw-rw---- 1 mysql mysql 593M Sep  1 17:17 flights#P#p16.MYI
-rw-rw---- 1 mysql mysql 500M Sep  1 17:14 flights#P#p4.MYI
-rw-rw---- 1 mysql mysql 494M Sep  1 17:14 flights#P#p2.MYI
-rw-rw---- 1 mysql mysql 489M Sep  1 17:15 flights#P#p8.MYI
-rw-rw---- 1 mysql mysql 482M Sep  1 17:14 flights#P#p6.MYI
-rw-rw---- 1 mysql mysql 481M Sep  1 17:14 flights#P#p5.MYI
-rw-rw---- 1 mysql mysql 480M Sep  1 17:14 flights#P#p7.MYI
-rw-rw---- 1 mysql mysql 478M Sep  1 17:14 flights#P#p3.MYI
-rw-rw---- 1 mysql mysql 465M Sep  1 17:20 flights#P#p20.MYI
-rw-rw---- 1 mysql mysql 464M Sep  1 17:18 flights#P#p18.MYI
-rw-rw---- 1 mysql mysql 416M Sep  1 17:22 flights#P#p27.MYI
-rw-rw---- 1 mysql mysql 381M Sep  1 17:23 flights#P#p28.MYI
-rw-rw---- 1 mysql mysql 348M Sep  1 17:15 flights#P#p10.MYI
-rw-rw---- 1 mysql mysql 288M Sep  1 17:23 flights#P#p29.MYI
-rw-rw---- 1 mysql mysql 102M Sep  1 17:14 flights#P#p1.MYI
-rw-rw---- 1 mysql mysql 459K Aug 31 15:34 weather.MYI
-rw-rw---- 1 mysql mysql  45K Aug 31 15:34 planes.MYI
-rw-rw---- 1 mysql mysql  18K Aug 31 15:34 carriers.MYI
-rw-rw---- 1 mysql mysql  17K Aug 31 15:34 airports.MYI

[beanumber]

FYI, the total footprint for the MySQL data is 23 Gb:

bbaumer@bbaumer-Precision-Tower-7810:~/dumps/airlines$ sudo du -h /var/lib/mysql
8.0K    /var/lib/mysql/mtcars
212K    /var/lib/mysql/performance_schema
23G /var/lib/mysql/airlines
1.1M    /var/lib/mysql/mysql
27G /var/lib/mysql

Plus the footprint for the raw data is 68 Gb, and that is after deleting most of the ZIP files.

bbaumer@bbaumer-Precision-Tower-7810:~/dumps/airlines$ sudo du -h ~/dumps
1.5G    /home/bbaumer/dumps/imdb
68G /home/bbaumer/dumps/airlines
91G /home/bbaumer/dumps

So the compression after inserting into MySQL, even after building the indexes, is about 3x.

[nicholasjhorton]

What's in here?

Nick

On Sep 1, 2015, at 5:30 PM, Ben Baumer [email protected] wrote:

27G /var/lib/mysql

Nicholas Horton Professor of Statistics Department of Mathematics and Statistics, Amherst College Box 2239, 31 Quadrangle Dr Amherst, MA 01002-5000 https://www.amherst.edu/people/facstaff/nhorton

[nicholasjhorton]

Classic time vs. space tradeoff.

If they were 10x the space, I'd start to be more concerned.

Nick

On Sep 1, 2015, at 5:26 PM, Ben Baumer [email protected] wrote:

The indexes currently take up more than twice as much disk space than the table itself. Is that bad?

Nicholas Horton Professor of Statistics Department of Mathematics and Statistics, Amherst College Box 2239, 31 Quadrangle Dr Amherst, MA 01002-5000 https://www.amherst.edu/people/facstaff/nhorton

[nicholasjhorton]

We should review which indices are most useful and which might be removed.