2

I have a table of items, each of it has an a_level, b_level, and an item_id. Any b_level is dedicated to only one a_level (example: b_level 14 is "child" of a_level 2 only)

Lets say we have million of items all of them are INSERTed once and then only SELECTs are requested.

If i SELECT an item based on item_id, then i need to index the item_id column. This will make the MySQL to look all millions of items, which is bad, since i already have the a_level and b_level information. So i guess if i SELECT an item based on a specific level and i have an index on that column, the MySQL will not have to look all millions of items, just the items with that particular level.

If i INDEX both on a_level, b_level (and of course item_id) and SELECT WHERE a_level= b_level= item_id= will it be bad? I guess only INDEX on b_level and item_id and SELECT WHERE b_level= AND item_id= will be enough/the best solution?

So, since i have a_level and b_level (which any b_level as i said is "child" of only one a_level) what will be the most efficient SELECT and INDEXes created for picking up an item?

w00h00
  • 21
  • 1
  • 2
    Please include SHOW CREATE TABLE items\G in your question along with sample queries rather than describing your table and queries in a textual narrative. – RolandoMySQLDBA Oct 12 '11 at 19:56

2 Answers2

1

Since your table only three columns, this setup can be straightforward.

Here is an ideal table layout for items

CREATE TABLE items
(
    item_id int not null auto_increment,
    a_level int not null,
    b_level int not null,
    KEY  ai  (a_level,item_id),
    KEY  bi  (b_level,item_id),
    KEY  abi (a_level,b_level,item_id),
    KEY  bai (b_level,a_level,item_id),
    PRIMARY KEY     (item_id)
);

Here are the realm of possible queries

  • SELECT * FROM items WHERE id = 100; (uses the PRIMARY KEY)
  • SELECT * FROM items WHERE a_level = 200; (uses KEY ai)
  • SELECT * FROM items WHERE b_level = 300; (uses KEY bi)
  • SELECT * FROM items WHERE a_level = 400 order by b_level (uses KEY abi ordering all b_level values under a_level 400 and orders item_id under each b_level)

YYou may try out other combinations of SELECT...GROUP BY...ORDER BY.... You should also run EXPLAIN on every SELECT query and see which indexes are summoned for query execution. Indexes that do not appear as a chosen index can be eliminated (dropped) over the course of time.

Here were questions you asked?

If i INDEX both on a_level, b_level (and of course item_id) and SELECT WHERE a_level= b_level= item_id= will it be bad? I guess only INDEX on b_level and item_id and SELECT WHERE b_level= AND item_id= will be enough/the best solution?

Those queries are not bad given the indexes I just presented.

The only reason why the item_id appears in all the indexes is to make those indexes covering indexes. That way, the retrieval of data is done using the index only, bypassing the table.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
0

Assuming your table structure is the following:

items
======================
a_level int not null
b_level int not null
item_id int not null

The most efficient index to search by item_id will be on item_id itself. You may want to have other indexes for searches on a_level or b_level when you don't know specific item_id you are looking for.

If you are uisng InnoDB, item_id is int or bigint, and you are not storing multiple rows with same item_id, then you would benefit in having the item_id set up as a primary key. InnoDB creates a clustered index on the primary key. If you don't have a primary key in InnoDB, a hidden row identifier is created for you; it will not be visible, but will take up disk space.

dabest1
  • 2,130
  • 15
  • 21