MySQL Indexes

To add an index or not?

[Birdman]

I'm rebuilding an older ecom DB that I designed (not so well) years ago.

My question is, should I index the `status` field?

What it does is define whether the product should be shown on the site or not. I'll be search like so:

select * from products where category = 12 and status = 1;

Here is the DB so far:

mysql> explain products;
+----------+-------------+------+-----+---------+-------+
¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+----------+-------------+------+-----+---------+-------+
¦ id ¦ varchar(30) ¦ ¦ PRI ¦ ¦ ¦
¦ category ¦ int(11) ¦ ¦ MUL ¦ 0 ¦ ¦
¦ name ¦ varchar(50) ¦ ¦ ¦ ¦ ¦
¦ descrip ¦ text ¦ ¦ ¦ ¦ ¦
¦ cost ¦ float(6,2) ¦ ¦ ¦ 0.00 ¦ ¦
¦ status ¦ tinyint(1) ¦ ¦ ¦ 1 ¦ ¦
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

[txbakers]

I'd say that if you are going to be using the status field as a condition in a lot of queries, an index that includes status would be helpful.

And if you are going to be using both fields for criteria, make an index with both fields.

[Birdman]

Thank you txbakers. It's not a huge table but I still want it to be as efficient as possible.

btw, I do have an index on category already.

[joelgreen]

Mysql does not use index if dataset is small because it is faster to do one by one check. If dataset is big - add index.

[eelixduppy]

It's not a huge table but I still want it to be as efficient as possible.

Just in case you weren't aware, mysql.com has nice documentation on mysql optimization [dev.mysql.com] if your willing to take a look :)
Good luck!