Hi, To optimize our queries we can use indexes. Can you please guide me how I can use indexes effectively. Do I deed to create indexes for the fields most used in where clause?
That's a good start, but not always the solution. You need to look at the number of rows in your tables, if very large you need to make sure the structure allows for scale such as not filtering in where clause on a txt field.
MySQL uses the index to find the smallest number of rows (the most selective index).If the table has a multiple-column index,any leftmost prefix of the index can be used by the optimizer to look up rows in MySQL.
It will all depends on what you want to queries. Take an example, if you query will always search for first_name + last_name, then you will need to create a compound index for them. You can create as many indexes as possible, the drawbacks is, it will increase your table size.