CODEWITHSUNDEEP
mysqlsqlperformance
azio
azio

Reputation: 585

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.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.

Please help.

EDIT:

Here are the result of EXPLAIN:

result of <code>EXPLAIN</code>

Upvotes: 1

Views: 116

Answers (2)

iWantSimpleLife
iWantSimpleLife

Reputation: 1954

Try changing to order of your joins. From the explain results, it shows that the index is only able to cut the images table to 1.4k records. So your joints are starting with that number. Maybe you can start the joints with the slug table (which i assume name is unique) and put the name conditional filter into the join's on portion.

You might also want to investigate on compound keys so that all fields used can be in the index. Key scanning is faster than file scanning.

Lastly, look into how your mysql is configured. Is it using temp table for the queries. Or worst, disk based temp tables.

It is a bit hard to tell cos we do not know the full extent of your database. Example, the size of all the tables, the amount of buffers set for keys, sort buffers, etc.

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

Move all the conditions you can into the ON clause of the joins:

SELECT ...
FROM images
JOIN images_locale ON images_locale.image_id = images.id
    AND images_locale.locale_id = 1
JOIN slugs ON images_locale.slug_id = slugs.id
    AND slugs.name = 'THE_SLUG_HERE'
JOIN categories_locale ON images.category_id = categories_locale.category_id
    AND categories_locale.locale_id = 1
JOIN users ON users.id = images.user_id
WHERE images.status = '1' 
LIMIT 1

The reason this works is that WHERE clauses filter the results of all possible joins, but if you move the conditions into the ON clauses, you avoid joining all following tables for rows you already know are not wanted.

This can avoid doing millions of unnecessary joins!

Upvotes: 1

Related Questions