will a primary index speed up my queries

Discussion in 'Databases' started by lanmonkey, Apr 9, 2009.

0
  1. #1
    I have a large table (8million+ rows) and I haven't se a primary index, quite simply because I don’t need to access the data by ID. I do however have a few standard btree index’s on the table.

    Will by queries on this table be faster if I create a primary index even if I don’t use it in queries?

    Thanks
     
    lanmonkey, Apr 9, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    No, it will not make any difference. A key is a key regardless if it is primary or not.

    You can also set one, and use explain to see if there is a difference, but if the id field is never used, then it shouldn't make any difference.
     
    jestep, Apr 9, 2009 IP
  3. qazu

    qazu Well-Known Member

    Messages:
    1,834
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    165
    #3
    No, you should base your index on your query. An index on a column that is not in the WHERE, ORDER BY, or GROUP BY clause won't have any improvement.
     
    qazu, Apr 9, 2009 IP
  4. MayurGondaliya

    MayurGondaliya Well-Known Member

    Messages:
    1,233
    Likes Received:
    38
    Best Answers:
    0
    Trophy Points:
    170
    #4
    Adding further, indexing will improve the performance, only if there is a large number of rows in the table.
     
    MayurGondaliya, Apr 9, 2009 IP
  5. qazu

    qazu Well-Known Member

    Messages:
    1,834
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    165
    #5
    Yup, lanmonkey's table had 8million+ rows
     
    qazu, Apr 10, 2009 IP
  6. harpreetjuit

    harpreetjuit Peon

    Messages:
    200
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #6
    indexing will help a lot on querying but at the same time performance will go down for updation, insertion and deletion...
     
    harpreetjuit, Apr 13, 2009 IP
  7. 2beers

    2beers Well-Known Member

    Messages:
    195
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    110
    #7
    this is so true. try not to create lots of indexes if you're not going to use them because it will slow down your script when you'll try to insert a new row.
     
    2beers, Apr 17, 2009 IP