0

I have read this question and I try to test it in my case, but I don't understand why it work.

I'm using rails and utf8mb4 encoded MariaDB (version 10.1.35)

I have this in my schema:

  create_table "settings", force: :cascade do |t|
    t.string   "var",         limit: 255,   null: false
    t.text     "value",       limit: 65535
    t.integer  "target_id",   limit: 4,     null: false
    t.string   "target_type", limit: 255,   null: false
    t.datetime "created_at",                null: false
    t.datetime "updated_at",                null: false
  end

  add_index "settings", ["target_type", "target_id", "var"],
       name: "index_settings_on_target_type_and_target_id_and_var",
       unique: true,
       length: {"target_type"=>191,
                "target_id"=>nil,
                "var"=>191}, using: :btree

I do rake db:schema:load and it pass without any problem. I think it should show error because innoDB index prefix limit is 767 bytes and we exceed the limit in this case:

191*4 + 191*4 = 1528 which exceed 767

Can anybody explain why it still work?

I'm struggling to understand the difference between index, key for index and prefix in index. It would be nice if someone can list a resource for me to learn these too.

What I have read:

Rick James
  • 135,179
  • 13
  • 127
  • 222
Henry Yang
  • 2,283
  • 3
  • 21
  • 38

2 Answers2

0

Index is the object that mantain the info for direct (or better access ) to rows .. for this the indexes store columns values the columns involved in indexes are named key .. the index prefix is a part (left side string) of the value in key for reduce the space for index .. ( if the prefix is always the same is unuseful )

191 x 4 = 764 (< 767)

192 x 4 = 768 (> 767)

the value 767 is per key and not total

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

so 191x4 for key target_type and 191x4 for key var work

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Can you please explain more about what index, key, prefix mean? And also I want to understand why it works with two 191. (I know that with one 192 will produce error because it exceed the 767 limit for innoDB, but don't know why two 191 will still work) – Henry Yang Aug 15 '18 at 06:32
  • I'm confused when reading this sentence "for this the indexes store columns values the columns involved in indexes are named key", can you please explain column values and key separately? And also explain how they related to the index? – Henry Yang Aug 16 '18 at 03:32
  • For this sentence "the index prefix is a part (left side string) of the value in key for reduce the space for index", it seems like you are saying index contain key, can you give an example of index that contain index to demonstrate this please? – Henry Yang Aug 16 '18 at 03:33
  • Sorry to be a pain but I don't really understand after reading your answer. If you know of any resource I can read to better understand the context and the problem, please point me to it too. Thank you! – Henry Yang Aug 16 '18 at 03:35
  • Index contain values that are related to columns table the columns involved in index are named keys ... so is impossible show an example of index than contain index ... an index is based on key column and contain the value of these columns .. – ScaisEdge Aug 16 '18 at 11:19
0

Each column of the index is limited to 767 bytes.

Try to avoid index prefixing (eg, INDEX(foo(191)), it is close to useless. See this for several workarounds.

Is this what the index looks like?

UNIQUE(target_type(191), target_id, var(191))

If so, there is another problem. The uniqueness constraint ignores the characters after the first 191 in target_type and var. So, you are probably not getting what you expected.

Rick James
  • 135,179
  • 13
  • 127
  • 222