GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Join with order by optimization

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Join with order by optimization

    Hi All,

    Bought the fantastic High Performance MySQL book.

    Came across a problem today that I can't really find a solution to. I'm not really at the point where I fully understand the indexes I'm trying to use.

    I've read the blog post about Order by optimizations and the mysql manual regarding it.

    I'm hoping for some more help of the forum, so here goes.

    I have a table with possible millions of products which link to a table with possible hundreds of categories. I want to join a category to the products table ordered by the products name.


    +----------------------------+| Tables_in_products_test |+----------------------------+| product | | category | | category_product | | ... |Table product+----------------+---------------------+------+-----+-------------------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+---------------------+------+-----+-------------------+----------------+| product_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment || product_name | varchar(255) | NO | | NULL | || ... |Table category+----------------+---------------------+------+-----+-------------------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+---------------------+------+-----+-------------------+----------------+| category_id | int(10) unsigned | NO | PRI | NULL | auto_increment || category_name | varchar(255) | NO | | NULL | || ... |Table category_product+----------------+---------------------+------+-----+-------------------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+---------------------+------+-----+-------------------+----------------+| category_id | int(10) unsigned | NO | PRI | NULL | || product_id | bigint(20) | NO | PRI | NULL | || ... |SELECT product.product_namer, category.category_nameFROM categoryINNER JOIN category_product USING(category_id)INNER JOIN product USING(product_id)WHERE category.category_id = 625ORDER BY product.product_nameLIMIT 25;


    I've tried all sorts of different combinations of indexes to try and get the queries to run fast.

    I managed to get the query to run fast if the category contained a large number of products. I guess just because it hit the limit 25 quicker.

  • #2
    If you are still having trouble with this, you should write back and include your EXPLAIN.

    Comment


    • #3
      Try this:

      SELECT
      product.product_namer,
      category.category_name

      FROM category

      INNER JOIN category_product USING(category_id)
      INNER JOIN product USING(product_id)

      WHERE
      product.category_id = 625

      ORDER BY
      product.product_name

      LIMIT 25;

      and add an index on (category_id,product_name).

      Comment

      Working...
      X