SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Serious MySQL Optimization Issue... help!

    Hi,

    So I'm providing a checklist feature to my users. I'm storing 267,000 "checked items." With each checked item, it has to be joined with another row to give complete user information on the interface. When I run this particular MySQL query, on my linux "TOP" command, MySQL suddenly begins to run at 100% CPU. Here's the deadly query:

    Code mysql:
    SELECT * FROM neoavatars_list l
        LEFT OUTER JOIN neoavatars_checked c
            ON l.avatar_id = c.c_aid AND c.c_uid = 1
        ORDER BY l.release_date DESC
    You might say, "you need to remove the evil select star," but truth be told I need every single column from from both tables. What's stumping me is, when I run a query such as this (which would have to search through all 267,000 rows):

    Code mysql:
    SELECT * FROM neoavatars_checked
    WHERE c_uid = 1
    It gives me my result instantly. So I know it's not that table that's messed up (it only has three columns, storing all numbers, actually). When I do a query on the other table ("neoavatars_list" which only has 400 rows), it also gives me a response instantly. But it seems that when these two tables are joined together, MySQL's CPU runs through the roof and it takes about 3 minutes for it to give me a result. How can I optimize that first query that I posted so it's... fast? Perhaps am I misusing joins or something? Or... what? I'm stumped.

    Thanks.
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what indexes have you created on those tables?

    i'm going to guess that you have declared primary keys with auto_increments

    have you declared an index on c_aid ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, only a primary key on the IDs with auto_increments.

    No, I haven't declared an index on c_aid. Please advise from here.

    Thanks in advanced!
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tipem View Post
    No, I haven't declared an index on c_aid. Please advise from here.
    my advice is, you should create an index on c_aid

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

  5. #5
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, Tipem!
    Basically you have to define index on anything that you are using in JOINS after WHERE or ORDERing BY.
    I recomend that you read among other index related things this:
    http://hackmysql.com/case1 and other optimisation related articles in http://hackmysql.com/documents
    And familiarize yourself with EXPLAIN

  6. #6
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, that fixed it. Thanks. D'oh.

    Now every time there is a change made to neoavatars_list, won't the index need to be updated? Is there a particular query you could run to update the index?
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tipem View Post
    Is there a particular query you could run to update the index?
    you don't have to, the database updates the indexes automatically
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •