SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql overload...

    hey guys.. im kinda confused now... I have a mysql table which has over 90,000 recods... I have the following mysql query :

    SELECT descr, link, ddate from links WHERE (descr like %bored%) ORDER BY ddate DESC LIMIT $offset, 100;

    now, the above query causes mysql load to sky rocket to 90%... but if i try the following :

    SELECT descr, link, ddate from links WHERE (descr like %bored%) LIMIT $offset, 100;

    it remains static between 15-20....

    the table is indexed on ddate.. both link, and descr are varchar(255) character columns.. and ddate is int(20) column storing a unix time stamp...

    any ideas on how i can get the order by to function without overloading mysql?

    thanks
    kunal
    i dunno...

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2001
    Location
    Earth
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well a couple of things. You can put "describe" in fron the the SQL and MySQL will tell you how it's processig the query. That might give you some indication of what's going on.

    Second, the only way MySQL can process "%bored%" is to scan the entire table. That is going to be a slow process.

    The order by will cause MySQL to sort the data. It seems quite strange that sorting at most 100 rows is killing MySQL.

    Frank
    http://www.serverexpert.com/ Discuss servers and the software that makes them work.
    http://www.webmasters-resources.com/ Resources for webmasters.

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Frank you give sound advice as always, and I am no expert in these matters. However, I understand that the problem is that in order to return the 100 results, mysql must first order the entire result set.

    Creating an index on ddate AND descr may help.

  4. #4
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey...
    i tried using both, describe and explain... both showed the worst thing, filesort

    i tried making both ddate, and descr indexs.. dint work.. i tried making each an index, dint work either... the load just sky rocketed...

    come to think of it.. should limit reduce the result set? isnt that the whole point of limit?
    i dunno...

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    kunal as I posed at DevShed, MySQL cannot use an index if you have '%$str%' vs '$str%'.

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by kunal
    i tried using both, describe and explain... both showed the worst thing, filesort
    when you do EXPLAIN, "Using filesort" isn't that bad. lots of queries have that. the thing that's bad, that is on yours, is the "Type" column -- it probably says "ALL." that means MySQL is doing a full table scan (read: no index being used). not good. but when you have a LIKE that starts with a wildcard, MySQL can't use an index, like MattR said.
    Last edited by DR_LaRRY_PEpPeR; Jan 29, 2002 at 15:12.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I by no means an a MySQL guru but, the situation here seems that the following is happening.

    When you do a wildcard search "where descr like %bored%" with a % in front MySQL HAS to do a full table scan. So the index won't be used. Someone stated this earlier.

    The ORDER BY compounds this problem because after the entire table scan. It has to take all of the qualifying rows (everything that matches the where clause) and then sort them after the table scan.

    If you can get rid of the wildcard in front of the LIKE clause that would help immensly. Also, I believe MySQL's newest version supports full-text indexing. You may want to look into that. It would speed things up quite a bit as well.

    Hope this helps.

    BlackCatt


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
  •