Could you explain aspect of using Composite MySQL INDEX for multiple INNER JOIN?

Issue

The query is

SELECT v.value
FROM products
         JOIN products_attributes_options pao
              ON 1 = 1
                  AND products.id = pao.product_id
         JOIN attributes_options ao
              ON 1 = 1
                  AND pao.attribute_option_id = ao.id
                  AND attribute_id = 12
         JOIN attributes_options_values aov
              ON
                      1 = 1
                      AND ao.id = aov.attribute_option_id
         JOIN `values` v
              ON
                      1 = 1
                      AND aov.value_id = v.id

         JOIN values_words vw_1
              ON
                      1 = 1
                      AND v.id = vw_1.value_id
         JOIN values_words vw_2
              ON
                      1 = 1
                      AND v.id = vw_2.value_id

         JOIN words w_1
              ON
                      1 = 1
                      AND vw_1.word_id = w_1.id
                      AND w_1.value LIKE '%a%'
         JOIN words w_2
              ON
                      1 = 1
                      AND vw_2.word_id = w_2.id
                      AND w_2.value LIKE '%a%'

GROUP BY v.id, v.value
ORDER BY v.id, v.value

For this part of query

JOIN products_attributes_options pao
     ON 1 = 1
        AND products.id = pao.product_id
JOIN attributes_options ao
     ON 1 = 1
        AND pao.attribute_option_id = ao.id
        AND attribute_id = 12

I have composite indexes

ix_aov_p (attribute_option_id, product_id)
ix_p_aov (product_id, attribute_option_id)

Via EXPLAIN my query I have seen – two of them could be possible keys.
But I thought only ix_aov_p is enough.

Could you explain how do composite indexes work for these cases?
Do I need two indexes or only one index? Which of them I need to use?
ix_aov_p or ix_p_aov

I have researched similar question, but there is not enough explanation for understanding – what happens?

Separate Join clause in a Composite Index – similar question – accepted answer didn’t explain enough what happens and why composite INDEX doesn’t work.

Glad to explanation what happens from the INDEX side with these JOINs for understanding composite INDEXes using in this case.


DB dump
Tables to use – DUMP (structure)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `attributes_options`
--

DROP TABLE IF EXISTS `attributes_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `attributes_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `attributes_options`
--

LOCK TABLES `attributes_options` WRITE;
/*!40000 ALTER TABLE `attributes_options` DISABLE KEYS */;
/*!40000 ALTER TABLE `attributes_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `attributes_options_values`
--

DROP TABLE IF EXISTS `attributes_options_values`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `attributes_options_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_option_id` int(11) NOT NULL,
  `value_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `attributes_options_values`
--

LOCK TABLES `attributes_options_values` WRITE;
/*!40000 ALTER TABLE `attributes_options_values` DISABLE KEYS */;
/*!40000 ALTER TABLE `attributes_options_values` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `products`
--

DROP TABLE IF EXISTS `products`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `products`
--

LOCK TABLES `products` WRITE;
/*!40000 ALTER TABLE `products` DISABLE KEYS */;
/*!40000 ALTER TABLE `products` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `products_attributes_options`
--

DROP TABLE IF EXISTS `products_attributes_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `products_attributes_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `attribute_option_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_p_aov` (`product_id`,`attribute_option_id`),
  UNIQUE KEY `ix_aov_p` (`attribute_option_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `products_attributes_options`
--

LOCK TABLES `products_attributes_options` WRITE;
/*!40000 ALTER TABLE `products_attributes_options` DISABLE KEYS */;
/*!40000 ALTER TABLE `products_attributes_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `values`
--

DROP TABLE IF EXISTS `values`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(190) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `values`
--

LOCK TABLES `values` WRITE;
/*!40000 ALTER TABLE `values` DISABLE KEYS */;
/*!40000 ALTER TABLE `values` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `values_words`
--

DROP TABLE IF EXISTS `values_words`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `values_words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value_id` int(11) NOT NULL,
  `word_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `values_words`
--

LOCK TABLES `values_words` WRITE;
/*!40000 ALTER TABLE `values_words` DISABLE KEYS */;
/*!40000 ALTER TABLE `values_words` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `words`
--

DROP TABLE IF EXISTS `words`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `words` (
  `id` int(11) NOT NULL,
  `value` varchar(190) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `words`
--

LOCK TABLES `words` WRITE;
/*!40000 ALTER TABLE `words` DISABLE KEYS */;
/*!40000 ALTER TABLE `words` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Tables visualization

Solution

That is much too normalized.

It is good to normalize product with product_id if the Products table has lots of info — name, size, etc. But all I see is name. So get rid of product_id and Products and just have a column product_name.

After you have done that, identify the many:many mapping tables. They need no id, but just two columns, each being either an id or a value. See this for advice on the optimal composite indexes for such tables: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

But I thought only ix_aov_p is enough.

That provides an efficient way for “given an aov, find the p values”. In other situations, you need the opposite order. Composite indexes are ordered. In the case of a mapping table like that, the “given” must come first.

In some other situations the ordering is important:

WHERE b > 4 AND a = 8

needs INDEX(a, b) in that order. It can quickly get to all the entries for a=8 and b=4, them scan forward until a>8. Think about searching for lastname=’James’ and firstname LIKE ‘R%’.

Meanwhile, either order of INDEX(a,b) is fine for

WHERE b = 4 AND a = 8

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Answered By – Rick James

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published