SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What's wrong with my query (very slow)?

    The following query is taking more than 20 seconds to execute on a table with around half million rows:

    Code:
        SELECT images.id, images.user_id, images_locale.filename, extension, size, width, height, views, batch, source, status, images.created_at, images.category_id, title, short_description, long_description, alternate, slugs.name as slug, images_locale.slug_id, path_cache AS category_path, full_name, users.username
        FROM images
        JOIN images_locale ON images_locale.image_id = images.id JOIN slugs ON images_locale.slug_id = slugs.id JOIN categories_locale ON images.category_id = categories_locale.category_id JOIN users ON users.id = images.user_id
        WHERE slugs.name = 'THE_SLUG_HERE' AND images.status = '1' AND images_locale.locale_id = 1 AND categories_locale.locale_id = 1
        LIMIT 1
    Now when I remove `slugs.name = 'THE_SLUG_HERE' AND` I get the result in a few milliseconds.

    This is my slug table:

    Code:
        CREATE TABLE `slugs` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
          `type` tinyint(4) NOT NULL,
          `locale_id` smallint(6) NOT NULL,
          PRIMARY KEY (`id`),
          UNIQUE KEY `name` (`name`)
        ) ENGINE=InnoDB AUTO_INCREMENT=3611900 DEFAULT CHARSET=utf8;
    I tried to `CREATE INDEX test_speed ON slugs(name)` but it didn't speed up things.

    Here's the result of EXPLAIN: http://i.stack.imgur.com/DnUzd.png

    Please help.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You already have an index on name, and it's being used in the query.
    You could try creating an index on (id, name) and see if that speeds things up? I don't know if that would work, you should try and see (and let me know the result ).

    Another thing you might try is put the condition on the slug name last in the WHERE clause. Maybe that'll cut down the number of rows to be examined before MySQL reaches the slug name condition.
    Edit: and put the slugs table JOIN last too.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Have a look at the EXPLAIN plan for the select statement. This will explain in more detail which index is being picked up. However, before doing that make sure that you have the table/index statistics up to date otherwise the query optimizer may be selecting a query path based on incorrect data. For instance, if there are 1,000,000 rows in slugs but the statistics are showing 1 row then it may decide a full scan is better than access via an index.

    To analyze statistics on a table issue:

    ANALYZE TABLE table_name;

    This would need to be done on each of the tables.


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
  •