SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard
    Join Date
    May 2003
    Location
    Berlin, Germany
    Posts
    1,829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Why is this query slow?

    Hey guys,

    sorry for the bad topic title, but I could not think of anything better. My problem is, that I have a really slow query, that I need help with:

    Code:
    SELECT `Assembly`.`id`, `Assembly`.`created` FROM `assemblies` AS `Assembly` WHERE `Assembly`.`account_id` = 'some_id';
    There are approx. ~160 000 rows that would match this. The whole table has 500 000 records and the query executes in like 48s! There is an index on 'account_id' and one on 'created'. Unfortunately there is no compound index.

    This is the output of EXPLAIN:

    Code:
    | id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows   | Extra       |
    +----+-------------+----------+------+---------------+------------+---------+-------+--------+-------------+
    | 1  | SIMPLE      | Assembly | ref  | account_id    | account_id | 97      | const | 157122 | Using where |
    For other account_id's that would contain less rows the query runs really fast. Can someone explain to me how mysql processes this query and why it is not using the account_id index? That would be great as it makes our production site really slow (for that particular client).

    Thanks a lot in advance.

    PS: The table engine is InnoDB.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by DarkAngelBGE View Post
    why it is not using the account_id index?
    It isn't?

    Do you really need to retrieve all 160k rows?

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    It ~is~ using the index, see value "account_id" under the column "key".

    It might be slow because MySQL has a hard time finding everything, or the index is too sparse (you might want to try a OPTIMIZE TABLE <table> -- beware, might take a while and make your server slow).

    What puzzles is me is why the key_len is 97 for just that one id. What datatype do you use for that id?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SitePoint Wizard
    Join Date
    May 2003
    Location
    Berlin, Germany
    Posts
    1,829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @guido2004: Shouldn't it say "using index" in the "Extra" column if it were using the index?

    @Scalio: Can't use optimize unfortunately. Will try it on the staging machine.

    id is char(32).

  5. #5
    SitePoint Wizard
    Join Date
    May 2003
    Location
    Berlin, Germany
    Posts
    1,829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it's not necessary to get all rows. In fact I only want those where created > something, but that ran even slower.

    Even select id from assemblies where account_id = 'some id' limit 50; is pretty slow.

  6. #6
    SitePoint Wizard
    Join Date
    May 2003
    Location
    Berlin, Germany
    Posts
    1,829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay a few more updates.

    With an optimize I could get the query to execute in 1min (8min before).

    This one here completes instantly:

    select id from assemblies where account_id = 'some_id';
    If I add any field to the list of retrieved fields, the query becomes horribly slow.

  7. #7
    SitePoint Wizard
    Join Date
    May 2003
    Location
    Berlin, Germany
    Posts
    1,829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anybody else having a good solution?

  8. #8
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT
          id
        , created
    FROM
        assemblies
    WHERE
        account_id = 'some_id'

    Just give that a try, it's near identical, I wonder if the query in the OP having tables qualified when only one table is involved may be confusing MySQL slightly, it may be expecting more then one table to be involved
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  9. #9
    SitePoint Enthusiast alexson's Avatar
    Join Date
    Jun 2010
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something you could do to speed up access is to ensure that all the fields in the table are fixed length. If you change VARCHARs to CHARs and don't use BOOL or TEXT fields each record will have a fixed size and traversing the table will be a LOT quicker.

    If there are TEXT fields or lots of other fields not need in most of the queries you could try moving that data to another table and joining it only when you need that information. But that depends on how you are using the data across the site as to whether that would be better overall rather than just for this query.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by alexson View Post
    If you change VARCHARs to CHARs and don't use BOOL or TEXT fields each record will have a fixed size and traversing the table will be a LOT quicker.
    no, not "a LOT quicker"

    extracting data from a variable length row is infinitesimally faster than extracting data from a fixed length row

    however, your suggestion for using CHAR instead of VARCHAR will bloat the size of the row

    this means fewer rows will fit onto a single disk block, which means that the query will have to read more disk blocks to retrieve all the same data

    and the speed of retrieving data blocks off the disk is humoungously slower than the speed you might have saved in not extracting data from a variable length row

    so in my opinion, this idea of using CHAR instead of VARCHAR is self-defeating

    Quote Originally Posted by alexson View Post
    If there are TEXT fields or lots of other fields not need in most of the queries you could try moving that data to another table and joining it only when you need that information.
    this, on the other hand, is an excellent suggestion

    TEXT columns are already stored "off row" so you don't need to worry about them, but separating other infrequently used columns into a related one-to-one table often has a significant effect on performance

    why? because the main table is now shorter, which means more rows fit onto a disk block, which means you can read more rows with fewer disk reads

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

  11. #11
    SitePoint Enthusiast alexson's Avatar
    Join Date
    Jun 2010
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I'd not considered the disk blocks themselves.


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
  •