Should i use index when using where clause in mysql?

i have a table that stores about 50k rows and here's my query :

select * from video where status = 1 and filter = 1 and category = 4

these co开发者_开发问答lumns are tinyint and i wonder should i do index on these columns ?

No!

You should always consider index selectivity! If the result of a where clause on an index will return more than 20% of the table, then the index is NOT used, but it's mantained.

So, for example, the status of your video will be 1, 2, 3, then you have 3 posible states. If those indexes are well distributed we can think that will be 33.3% of them aprox. Then, the query:

select * from video where status = 1

will NOT use that index! Instead, a FULL TABLE SCAN would be used.

Please, read about it. It's really important and a common mistake.

Google search

If you create an index for this query, try (status, filter, category) (the order may be different).

But it depends on what percent of your table rows have (status, filter, category) = (1,1,4). The smallest percent, the better your query will run using the index. As santiagobasulto noted, if the index selectivity is not high, the index will not be used for the query anyway but a full scan of the table will be run.

I would definitely use indexes. They make the select faster. But then if there are a lot of inserts into the table, then I would think again. Indexes on insert statements adds a lot of overload and your insert statement will be slow as it has to restructure the indexes every time you make an insert.