How should I use MySql Indexes?

Discussion in 'MySQL' started by Make a perfect site, Aug 25, 2011.

0
  1. #1
    Hi all,

    I have a table with about 100 records in it.
    Some rows contain very much data (100+ words). So the whole table contains loads of valuable rows.

    I also have a PHP file which selects the rows from the db. It takes about 10-12 seconds to load.

    This is why I thought about indexing the table. How could I do this? I know it would be a lot faster.

    Do I have to modify the php code as well?

    Any help appreciated.

    Thanks in advance.

    Best Regards,
    Akos
     
    Make a perfect site, Aug 25, 2011 IP
  2. freelanceinphp

    freelanceinphp Member

    Messages:
    134
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    48
    #2
    For indexing you do not need to change in your php code.
     
    freelanceinphp, Aug 25, 2011 IP
  3. freelanceinphp

    freelanceinphp Member

    Messages:
    134
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    48
    #3
    Indexing should apply only those columns on which you written where clause.
     
    freelanceinphp, Aug 25, 2011 IP
  4. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #4
    Indexing helps for a "where x = y". If you "select * from table", it won't make it faster. It also won't make it faster if you use LIKE instead of =.

    With 100 records, you should be able to do a "select *" in about the time it takes you to get the Enter key all the way down. You have something else slowing things down. (Some hosts have very fast hosting but very slow databases.)
     
    Rukbat, Aug 25, 2011 IP
  5. le_punk

    le_punk Peon

    Messages:
    31
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    i think you have a problem either with your select or with your server. a query shouldn't take that long on 100 rows, no matter how big the data is (well if you store 1gig blobs in it it might be slow due to bandwith)

    the best idea is to run an explain on your query (just run mysql from command line, or from mysql benchmark and put an EXPLAIN infront of your query) and check the output. if your query is doing some complicated search on the field having the large amount of text you might have to consider adding a full text index on it
     
    le_punk, Aug 26, 2011 IP
  6. mmerlinn

    mmerlinn Prominent Member

    Messages:
    3,197
    Likes Received:
    819
    Best Answers:
    7
    Trophy Points:
    320
    #6
    A table with only 100 records should not take that long to load.

    And indexing such a short table will not noticeably speed things up.

    There is something wrong with your code that is slowing things to a crawl. Fix that problem and you won't need to index your table nor write the indexing code.
     
    mmerlinn, Aug 28, 2011 IP
  7. zfvu

    zfvu Peon

    Messages:
    33
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Develop cache system for your website. It's faster and safer. You're going to have data stored in 2 different places - MySQL and .HTML / .TXT. Google for - php mysql cache.
     
    zfvu, Aug 31, 2011 IP