How to properly index mysql database?

Question

I am looking to display database entries based on category and if a specific field is null or not. How do I properly index for best performance?

As the specific field is quite long at 1000 characters, I do not want to index on it, just on if the field is null or not.

Answer 1

It's not a good Idea to make an index to a field , if you just want to find which record contains null and not null .
Index are not useful when the 'record variant' is to little. Because if an index is used then finding matching records is a two-step process.
- First, your query must searching the index for the right record.
- Second, it must find the record itself on disk.
without index, query can make 'sequential scan' through the records in storage order and throw those which don't match with the Query condition. In this case, not using index is faster then using an Index.
Little 'record variant' can we found in boolean field (just true and false), a field which contains only 'men' and 'woman' , or a field which contains 'state' for 100,000 people record.

Answer 2

Generally speaking, any field that you expect to search by, sort by, or join on should be indexed. So in your case, I'd say you need an index for the category field, as well as for the "specific field" you mentioned.

Answer 3

Here's a good article on optimizing mysql.. http://www.databasejournal.com/features/mysql/article.php/1382791

Answer 4

are you trying to execure any query? or check whether you have any tables added in the DB