1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

effective use of indexes in mysql

Discussion in 'Databases' started by Jason.pb, Dec 28, 2016.

  1. #1
    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?
    SEMrush
     
    Jason.pb, Dec 28, 2016 IP
    SEMrush
  2. gallitin

    gallitin Well-Known Member

    Messages:
    667
    Likes Received:
    22
    Best Answers:
    2
    Trophy Points:
    105
    #2
    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.
     
    gallitin, Jun 2, 2017 IP
  3. K Padmapriya

    K Padmapriya Banned

    Messages:
    53
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    38
    #3
    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.
     
    K Padmapriya, Nov 6, 2017 IP
  4. chrislim2888

    chrislim2888 Active Member

    Messages:
    45
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #4
    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.
     
    chrislim2888, Nov 13, 2017 IP