Could you explain aspect of using Composite MySQL INDEX for multiple INNER JOIN?
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 */;
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
- MySql can't make column auto_increment
- Insert auto increment primary key to existing table
- 3 mysql table join but not getting expected result that i want
- MySQL insert_id returning null even though there was an insert
- mysql select query within a serialized array
- MySQL: Large VARCHAR vs. TEXT?
- php_network_getaddresses: getaddrinfo failed error in Docker's adminer
- Correlated queries - In derived tables, can I have many nesting levels?
- Is there a MySQL option/feature to track history of changes to records?
- When is a database called as an Embedded database?
- Multiple foreign keys from one table to same field in another table in Laravel
- Datetime-value's time inserted incorrectly in one column but not in another MYSQL
- Syntax diagrams for MySQL
- Converting currency symbols '$' '£' to html code $ £?
- Create Index in mysql not working
- Incorrect string value: '\xEF\xBF\xBD' for column
- Understanding inner join in recursive CTE (MySQL)
- Update MySQL table with rank number of duplicates by name
- Mysql query for 3 tables & user meta
- Setting up new instance of MySQL what is Current Root Password?
- How to change the CHARACTER SET (and COLLATION) throughout a database?
- How to resolve circular foreign key constraints with NOT NULL in Hibernate
- mysql join to record first one which is not matched before
- Conditional statement in migration doesn't work laravel
- Mysql ERROR at line 1153: Unknown command '\'
- How do I find the command causing a replication error in MySQL
- Wordpress Meta Value (Serialized) Filter
- Can you access the auto increment value in MySQL within one statement?
- Can't connect to RDS instance from EC2 instance
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)