Results 1 to 3 of 3
Oct 7, 2012, 19:12 #1
- 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.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
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;
Here's the result of EXPLAIN: http://i.stack.imgur.com/DnUzd.png
Oct 7, 2012, 22:59 #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 ).
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.
Oct 8, 2012, 07:29 #3
- 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.