Results 1 to 17 of 17
  1. #1
    jessie is offline Member
    Join Date
    Nov 2010
    Posts
    29
    Rep Power
    0

    Default MySQl query taking too much time

    Here is my MySQL query..it takes a lot of time to execute and show results. the results is the content of a set of 24000 articles.

    SELECT Content FROM AQUA_MASTER WHERE Article_ID IN (SELECT Article_ID FROM AQUA_TAGGING WHERE Tag_ID=1736);

    why is that? can somebody tell me how i can solve this problem?

    jessie

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    First off is there an index on Tag_ID?
    Then is there an index on Article_ID?

    How big are the two tables involved?

    Finally, what classes as a long time?

  3. #3
    jessie is offline Member
    Join Date
    Nov 2010
    Posts
    29
    Rep Power
    0

    Default

    no.there are no indexes on either. the tables are too big ,containing around 800,00 rows each. to execute this query it takes around 5 minutes...

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Add indexes.

    Without them MySQL has to trawl every row of the Aqua_Tagging table to check the tag_ID field. That's a huge amount of unecessary work.
    Once it's done that, it then has to trawl the Aqua_Master table from beginning to end for each result returned in the first part to find the articles.

  5. #5
    jessie is offline Member
    Join Date
    Nov 2010
    Posts
    29
    Rep Power
    0

    Default

    the indexes are created...but still it is slow....what could be the reason?

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    And how long does it take now?

    Also, how are you timing it?

    ETA: WHile we're at it, what is the Content field?

  7. #7
    jessie is offline Member
    Join Date
    Nov 2010
    Posts
    29
    Rep Power
    0

    Default

    the time appears in the sqlyog interface. it takes around 2 min and 10 seconds now... but still its late...the content field contains text of articles...

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    So they're long text fields?
    What happens if you select a really small field (like Article_id)?
    I'm just wondering if it's the volume of data....then again, SQLyog only transfers about 50-100 rows on a query, but it still might be the problem.

    Other than that you'll probably want to look at what the execution plan is for the query. Can't help you on how to do that, but the MySQL docs should have the info. The plan should show whether it is using both indexes.

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Here you go.
    Explain.

    So you'll want to run:
    EXPLAIN SELECT Content FROM AQUA_MASTER WHERE Article_ID IN (SELECT Article_ID FROM AQUA_TAGGING WHERE Tag_ID=1736)

    possibly with the EXTENDED keyword as well.

    As for interpreting the results...:)

  10. #10
    jessie is offline Member
    Join Date
    Nov 2010
    Posts
    29
    Rep Power
    0

    Default

    it luks like this.

    EXPLAIN EXTENDED RESULT i dont understand it..does it use the indexes?

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    The subquery is, the main query isn't.
    Actually the index on tagId is unecessary since it appears to be the primary key, so is automatically indexed.

    Are you sure there's an index on Article_ID?
    Do a simple query on Aqua_master where article_id = some number and see what the explain gives for that.

  12. #12
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Curious.
    Testing against a table I have here (names changed to protect the innocent):
    Java Code:
    select *
    from some_table
    where some_indexed_field = 1
    uses the index.
    As does:
    Java Code:
    select *
    from some_table
    where some_indexed_field in (1,2,3)
    But this does not:
    Java Code:
    select *
    from some_table
    where some_indexed_field in (select some_other_indexed_field from some_other_table)
    The subquery index is used, but the main query one is not.

    At this point I can only suggest going to a MySQL forum.

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    OK, there is a problem with IN and subqueries in MySQL, and has been for years.

    Use a JOIN:
    Java Code:
    SELECT Content
    FROM AQUA_MASTER am, AQUA_TAGGING at
    WHERE am.Article_ID = at.Article_ID
      AND at.Tag_ID=1736
    ETA: Thinking about it I should have suggested that in the first place (along with the index stuff).
    Last edited by Tolls; 11-11-2010 at 11:22 AM.

  14. #14
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Correct. It doesn't, because it doesn't know, beforehand, what those values are.

    Try doing

    Java Code:
    select some_table.*
    from some_table, some_other_table
    where some_table.some_indexed_field = some_other_table.some_other_indexed_field
       and some_other_table.some_other_confining_field = 'some_value'
    or
    Java Code:
    SELECT some_table.* FROM some_table LEFT JOIN (some_other_table)
        ON (some_table.some_indexed_field = some_other_table.some_other_indexed_field
              and some_other_table.some_other_confining_field = 'some_value')
    )
    Edit: Far too slow. Also, that problem exists in most DB's since they cannot get a "handle" on the values and so cannot create their action plan properly (which the action plan for an IN is determined differently than for a join, of course).

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Yep.
    And I never encounter it because I don't write my queries like that...
    One of those things I never end up thinking about.

  16. #16
    jessie is offline Member
    Join Date
    Nov 2010
    Posts
    29
    Rep Power
    0

    Default

    hi tolls,

    the query
    SELECT Content
    FROM AQUA_MASTER am, AQUA_TAGGING at
    WHERE am.Article_ID = at.Article_ID
    AND at.Tag_ID=1736

    works in 23 seconds..the others took a lot of time still...but anyway i think 23 seconds is much better. thnks guys for your help..... :)

  17. #17
    FON
    FON is offline Senior Member
    Join Date
    Dec 2009
    Location
    Belgrade, Serbia
    Posts
    368
    Rep Power
    5

    Default

    Quote Originally Posted by jessie View Post
    works in 23 seconds..
    I wish i could participate in this thread earlier...
    One thing is sure - you got yourself good optimization lesson :)

    Do you have to run this query in runtime?
    If not, simply prepare it earlier, place it in some help table and use SELECT when you need it. This is common practice for report creation.

    Maybe you can play with your server configuration and
    try to improve this. I had positive experience changing few config params while working on Postgres DB.
    As for MySQL, in doc i found 'key_buffer_size' and 'table_cache' as performance related. Try them.

    There are not many columns so using VIEWS is not an option here, but can you do any 'horizontal partitioning' and split big tables and use union on them when needed?
    Or use 'vertical partitioning' and create this tables with small number of columns and use additional tables to store remaining columns.

    Perfect time for you to play, explore and learn!

Similar Threads

  1. MySQL JDBC query returns no response
    By PDXErik in forum New To Java
    Replies: 1
    Last Post: 08-20-2010, 09:07 AM
  2. Query MySQL
    By boss in forum Advanced Java
    Replies: 3
    Last Post: 01-04-2010, 09:36 AM
  3. Query MySQL
    By boss in forum Advanced Java
    Replies: 3
    Last Post: 01-04-2010, 09:35 AM
  4. mysql query performance issue
    By gilbertsavier in forum JDBC
    Replies: 0
    Last Post: 08-05-2009, 10:34 AM
  5. MySQL/JDBC Mysql query output
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-13-2009, 01:57 AM

Posting Permissions

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