¿Cómo evitar el “uso temporal” en consultas de muchos a muchos?

Esta consulta es muy simple, todo lo que quiero hacer es obtener todos los artículos en la categoría dada ordenados por el campo last_updated :

 SELECT `articles`.* FROM `articles`, `articles_to_categories` WHERE `articles`.`id` = `articles_to_categories`.`article_id` AND `articles_to_categories`.`category_id` = 1 ORDER BY `articles`.`last_updated` DESC LIMIT 0, 20; 

Pero funciona muy lento. Esto es lo que dijo EXPLAIN:

 select_type table type possible_keys key key_len ref rows Extra -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SIMPLE articles_to_categories ref article_id,category_id article_id 5 const 5016 Using where; Using temporary; Using filesort SIMPLE articles eq_ref PRIMARY PRIMARY 4 articles_to_categories.article_id 1 

¿Hay alguna manera de reescribir esta consulta o agregar lógica adicional a mis scripts PHP para evitar el Using temporary; Using filesort Using temporary; Using filesort y acelerar las cosas?

La estructura de la tabla:

 *articles* id | title | content | last_updated *articles_to_categories* article_id | category_id 

ACTUALIZAR

He last_updated indexado. Supongo que mi situación se explica en la documentación :

En algunos casos, MySQL no puede usar índices para resolver el ORDER BY, aunque todavía usa índices para encontrar las filas que coinciden con la cláusula WHERE. Estos casos incluyen lo siguiente:

La clave utilizada para recuperar las filas no es la misma que la utilizada en ORDER BY: SELECT * FROM t1 WHERE key2 = constante ORDER BY key1;

Se está uniendo a muchas tablas, y las columnas en ORDER BY no son todas de la primera tabla no constante que se utiliza para recuperar filas. (Esta es la primera tabla en la salida EXPLAIN que no tiene un tipo de combinación const).

pero aún no tengo idea de cómo arreglar esto.

Aquí hay un ejemplo simplificado que hice para una pregunta relacionada con el rendimiento similar que hace algún tiempo se aprovecha de innodb clúster de índices de claves primarias (obviamente, solo está disponible con innodb !!)

Tienes 3 tablas: categoría, producto y categoría de producto de la siguiente manera:

 drop table if exists product; create table product ( prod_id int unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb; drop table if exists category; create table category ( cat_id mediumint unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb; drop table if exists product_category; create table product_category ( cat_id mediumint unsigned not null, prod_id int unsigned not null, primary key (cat_id, prod_id) -- **note the clustered composite index** !! ) engine = innodb; 

Lo más importante es el orden de la clave primaria compuesta agrupada de product_catgeory, ya que las consultas típicas para este escenario siempre están encabezadas por cat_id = x o cat_id en (x, y, z …).

Tenemos 500K categorías, 1 millón de productos y 125 millones de categorías de productos.

 select count(*) from category; +----------+ | count(*) | +----------+ | 500000 | +----------+ select count(*) from product; +----------+ | count(*) | +----------+ | 1000000 | +----------+ select count(*) from product_category; +-----------+ | count(*) | +-----------+ | 125611877 | +-----------+ 

Así que veamos cómo funciona este esquema para una consulta similar a la suya. Todas las consultas se ejecutan en frío (después del reinicio de mysql) con búferes vacíos y sin caché de consultas.

 select p.* from product p inner join product_category pc on pc.cat_id = 4104 and pc.prod_id = p.prod_id order by p.prod_id desc -- sry dont a date field in this sample table - wont make any difference though limit 20; +---------+----------------+ | prod_id | name | +---------+----------------+ | 993561 | Product 993561 | | 991215 | Product 991215 | | 989222 | Product 989222 | | 986589 | Product 986589 | | 983593 | Product 983593 | | 982507 | Product 982507 | | 981505 | Product 981505 | | 981320 | Product 981320 | | 978576 | Product 978576 | | 973428 | Product 973428 | | 959384 | Product 959384 | | 954829 | Product 954829 | | 953369 | Product 953369 | | 951891 | Product 951891 | | 949413 | Product 949413 | | 947855 | Product 947855 | | 947080 | Product 947080 | | 945115 | Product 945115 | | 943833 | Product 943833 | | 942309 | Product 942309 | +---------+----------------+ 20 rows in set (0.70 sec) explain select p.* from product p inner join product_category pc on pc.cat_id = 4104 and pc.prod_id = p.prod_id order by p.prod_id desc -- sry dont a date field in this sample table - wont make any diference though limit 20; +----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+ | 1 | SIMPLE | pc | ref | PRIMARY | PRIMARY | 3 | const | 499 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | vl_db.pc.prod_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+ 2 rows in set (0.00 sec) 

Entonces eso es 0.70 segundos frío – ouch.

Espero que esto ayude 🙂

EDITAR

Después de haber leído su respuesta a mi comentario anterior, parece que tiene una de dos opciones para hacer:

 create table articles_to_categories ( article_id int unsigned not null, category_id mediumint unsigned not null, primary key(article_id, category_id), -- good for queries that lead with article_id = x key (category_id) ) engine=innodb; 

o.

 create table categories_to_articles ( article_id int unsigned not null, category_id mediumint unsigned not null, primary key(category_id, article_id), -- good for queries that lead with category_id = x key (article_id) ) engine=innodb; 

depende de sus consultas típicas sobre cómo define su PK agrupado.

Debería poder evitar filesort agregando una clave en articles.last_updated . MySQL necesita el filesort para la operación ORDER BY, pero puede hacerlo sin filesort siempre que ordene por una columna indexada (con algunas limitaciones).

Para obtener mucha más información, consulte aquí: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Supongo que ha hecho lo siguiente en su db:

1) artículos -> id es una clave principal

2) articles_to_categories -> article_id es una clave externa de artículos -> id

3) puedes crear un índice en category_id

 ALTER TABLE articles ADD INDEX (last_updated); ALTER TABLE articles_to_categories ADD INDEX (article_id); 

Deberías hacerlo. El plan correcto es encontrar los primeros pocos registros usando el primer índice y hacer el JOIN usando el segundo. Si no funciona, pruebe STRAIGHT_JOIN o algo para aplicar el uso adecuado del índice.

    Intereting Posts