beanumber/airlines

optimize indexes?

Open

#26 opened on Sep 1, 2015

View on GitHub
 (4 comments) (0 reactions) (0 assignees)R (36 forks)github user discovery
help wantedquestion

Repository metrics

Stars
 (24 stars)
PR merge metrics
 (No merged PRs in 30d)

Description

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

Contributor guide

Research direction
Inspect the table schema, index definitions, and query workload to identify redundant or unused indexes, and consider index consolidation or using partial indexes.
Tech stack
rsql
Domain
database
Issue type
Performance
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
2
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
1-3 hours
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
Active
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
Mostly clear
Prerequisites
SQL
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
60