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

Databases
1

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

    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:

    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

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 :wink: ).

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

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.