SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    0 Post(s)
    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:

        SELECT, 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, 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 = JOIN slugs ON images_locale.slug_id = JOIN categories_locale ON images.category_id = categories_locale.category_id JOIN users ON = images.user_id
        WHERE = '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 ` = 'THE_SLUG_HERE' AND` I get the result in a few milliseconds.

    This is my slug table:

        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`)
    I tried to `CREATE INDEX test_speed ON slugs(name)` but it didn't speed up things.

    Here's the result of EXPLAIN:

    Please help.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    163 Post(s)
    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
    San Sebastian, Spain
    1 Post(s)
    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.


Posting Permissions

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