SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to build index in MYSQL

    Index in mysql.

    I have a table in mysql database which has a very large number of rows and can be expanded. Currently 300,000 rows and can reach million records in the future. users perform a searche on the table from a php page. I update it with new records once a week roughly and I also delete and add more records so it can reach very large number of records. Currently I am not using indexes and it is very slow when a search is made. I have a query that perform the search and it has a few conditions. I want to use indexes to optimise performance. below is the query style. I am just showing the structure using an example as the true values are not shown:


    select value1, value2, value3,value4 from tablename where objectID like %searchstring%
    If valuex == 'x'
    {sql = 'AND valuex = $variablevalue'


    else if valuey == y
    {sql = 'AND valuey = $variablevalue'

    etc

    To create an index would be on the where clause as the search is made on the objectID. correct?

    but users can select other options from the drop down values as valuex or valuey. so what is the best way to create an index to speed up the sql search?


    2) As I will be updating the table once a week, how do i rebuild the indexes?

    thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a LIKE string with a leading wildcard will always result in a slow query

    you can try to create an index on objectID and hope that this gives you an index scan instead of a table scan, but doing before & after EXPLAINs will soon resolve that question

    the only other thing that might improve things is an index on valuex and/or valuey
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the only other thing that might improve things is an index on valuex and/or valuey
    do i create a separte index on ech of them? I mean create an index on ObjectID and then another index on valux etc?

    What do you mean by EXPLAIN?


    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    an index on ObjectID, and a separate index on valuex, or alternatively a separate index on (valuex,valuey), or possible an additional separate index on valuey

    --> EXPLAIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds a good option

    Thanks

  6. #6
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    , I will be updating and deleting records weekly. How do I drop and rebuid the indexes:?
    Is there a syntax for that? .

    Secondly, is there a way of checking whether indexes are being used ? and how they improve perfromance?


    thanks
    Last edited by Mittineague; Jan 20, 2011 at 15:38. Reason: <sig link removed />

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy linked up EXPLAIN syntax, you should read what it says on that link. They explained it all there.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •