slow load

Discussion in 'Databases' started by Mahir1505, Feb 6, 2010.

0
  1. #1
    Hello,

    Since i got more than 120.000 entries in my database (over 20MB) everything i put in database, or if i want to read something, i takes long long...

    Anyone got any tips to make it faster load?

    Thanks
     
    Mahir1505, Feb 6, 2010 IP
  2. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    Did you properly index your columns?
     
    mwasif, Feb 6, 2010 IP
  3. Mahir1505

    Mahir1505 Peon

    Messages:
    11
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    thanks for your answer...

    I think so, only i don't understand whats the different of
    " WHERE name = '$name' "
    AND
    " WHERE name LIKE '%string%' "

    If i use "LIKE" will that change something?
     
    Mahir1505, Feb 6, 2010 IP
  4. hostgenius

    hostgenius Guest

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    use there are tools u can use to measure the time it takes to execute your commands.

    google them, use them to play around and see what gives u the best result.

    LIKE '%something%' is the "%" is a wildcard like u would do *something* in a windows search :)

    u know what i mean or not ?
     
    hostgenius, Feb 8, 2010 IP
  5. pinkretard

    pinkretard Peon

    Messages:
    96
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Without knowing anything about structure and queries, it's hard to tell.

    My MySQL database has over 130MB, over 1M of records and still it's pretty fast. Surely, it's on a dedicated server, but anyway...
     
    pinkretard, Feb 14, 2010 IP
  6. duben

    duben Active Member

    Messages:
    50
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    61
    #6
    in performance compare

    " WHERE name = '$name' "
    AND
    " WHERE name LIKE '%string%' "

    always use = is it's possible. Wildcard need more sources.

    Without background knowledge of you DB, MySQL has tool that shows you statistics of queries, slow queries, wrongly optimized etc.

    There is no general rules, that you can use. Every professional optimization is based on "it depends" ... what you want ...

    Basic RULE analyze and then Optimize

    Some basic hints:
    1. Use cluster index on correct fields, use index on other fields (be careful and not over-optimize). Usually you need index on fields you are using in often queries in WHERE conditions and JOINs ... remember, index can increase read speed, but slower inserts and updates.

    2. Do you have enough hardware or allocated resources? Do you have enough RAM, disk size, disk speed, how its with CPU work load ... etc.

    3. Are application correctly written? Lot of PHP code using programmer style not database style. Call lot of SQL queries in loops are wrong, rewrite code to do rather all changes in one query.

    4. Are you writing SQL proper way? Not using SELECT * FROM always specify only columns you need. In WHERE condition use first condition that most data amount, then second larger reduction etc.

    5. Do not use IN, use Exists, its much faster

    etc.... there are many techniques. If you are not familiar with SQL and Databases to much, order some analysts or DB expert, it can helps you a lot.
     
    duben, Feb 15, 2010 IP