1

My query does not seem to bother for using indexes. Collations are the same on each table.

EXPLAIN SELECT SUM(it.revenue) FROM invoice_items AS it 
INNER JOIN invoice AS i ON i.id = it.invoice_id
WHERE i.customer_id = 123
AND it.manufacturer_id = 45
AND i.created_at BETWEEN '2017-01-01' AND '2017-12-31'

It should have only 400 rows, and instead, it counts all possible rows.

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "i" "ref"   "PRIMARY,created_at,id_customer_id,customer_id" "customer_id"   "4" "const" "36464" "Using where"
"1" "SIMPLE"    "it"    "ref"   "invoice_id,manufacturer_id,invoice_id_manufacturer_id" "invoice_id"    "4" "comercial.i.id"    "1" "Using where"

Tried even force and use index syntaxes, but no luck, what am I doing wrong?

Update: Adding show create tables. For invoice referenced as i I'm trying to use id_customer_id

invoice

    CREATE TABLE `invoice` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `system` int(3) unsigned DEFAULT NULL,
  `number` bigint(20) unsigned NOT NULL,
  `customer_id` int(11) unsigned NOT NULL,
  `shipping_point_id` int(11) unsigned NOT NULL,
  `dv` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `drv` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ctv` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `rv` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `transportation_zone_id` int(11) unsigned DEFAULT NULL,
  `invoice_type` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `invoice_text` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `payment_terms` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contract_discount` decimal(5,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `created_at` (`created_at`),
  KEY `invoice_type` (`invoice_type`),
  KEY `system` (`system`),
  KEY `transportation_zone_id` (`transportation_zone_id`),
  KEY `number` (`number`),
  KEY `customer_id` (`customer_id`),
  KEY `customer_id_created_at` (`customer_id`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=3419336 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

invoice_items

   CREATE TABLE `invoice_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) unsigned NOT NULL,
  `position` int(11) unsigned NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `material` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `material_group` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mstav` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `manufacturer_part_number` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `manufacturer_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `plant` int(11) DEFAULT NULL,
  `revenue` decimal(10,2) DEFAULT NULL,
  `margin` decimal(10,2) DEFAULT NULL,
  `currency` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `margin_percentage` decimal(10,2) DEFAULT NULL,
  `disc_contractdisct` decimal(10,2) DEFAULT NULL,
  `nrcdaaprclientbstkd` int(11) DEFAULT NULL,
  `pret_mediu_variabilverpr` double(10,2) DEFAULT NULL,
  `valza90cdaza90c` double(10,2) DEFAULT NULL,
  `valzp0ccdazp01c` double(10,2) DEFAULT NULL,
  `prcza90cdaza90c_p` double(10,2) DEFAULT NULL,
  `prczp01cdazp01c_p` double(10,2) DEFAULT NULL,
  `pretlistaprlst` double(10,2) DEFAULT NULL,
  `valra00cdara00c` double(10,2) DEFAULT NULL,
  `valza91za91f` double(10,2) DEFAULT NULL,
  `ordered_by` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `manufacturer_id` (`manufacturer_id`),
  KEY `material` (`material`),
  KEY `manufacturer_part_number` (`manufacturer_part_number`),
  KEY `invoice_id_manufacturer_id` (`invoice_id`,`manufacturer_id`),
  KEY `manufacturer_id_invoice_id_revenue` (`manufacturer_id`,`invoice_id`,`revenue`)
) ENGINE=InnoDB AUTO_INCREMENT=6385754 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Update 2: After this all my selects went down from 36k to 4k of rows filtered, exactly what I needed.

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "i" "range" "PRIMARY,created_at,customer_id,customer_id_created_at" "customer_id_created_at"    "9" \N  "4818"  "Using where; Using index"
"1" "SIMPLE"    "it"    "ref"   "invoice_id,manufacturer_id,invoice_id_manufacturer_id,manufacturer_id_invoice_id_revenue"  "invoice_id"    "4" "comercial.i.id"    "1" "Using where"
  • 3
    Provide SHOW CREATE TABLE invoice_items and SHOW CREATE TABLE invoice. Also please let us know which indexes you are trying to force to use. – Willem Renzema Dec 3 '18 at 17:08
  • My query does not seem to bother for using indexes. Explain claims that indices by i.customer_id and by it.invoice_id are used... see key column of EXPLAIN. – Akina Dec 3 '18 at 18:33
  • 1
    Only one index per table can be used. How MySQL uses indexes, so you need compound indexes. Seems like invoice(customer_id,created_at) and invoice_items(manufacturer_id, invoice_id) (maybe swapped order - depends which mysql chooses first) would be the most productive indexes for this query. – danblack Dec 3 '18 at 22:56
  • Added (manufacturuer_id,invoice_id,revenue) index on invoice_items and (customer_id,created_at) on invoice so now everything is great! – Szabi Zsoldos Dec 4 '18 at 9:40
1

Try adding the index (manufacturuer_id,invoice_id,revenue) to the invoice_items table.

This should help nudge the optimizer in the direction of using your existing id_customer_id index in the invoice table.

To see why it is not currently using the id_customer_id index, consider what it would need to do so. Since id is the leftmost column, that means you must have some information about the id column in order for it to gain much benefit from the index. As there is nothing in the WHERE clause about the id column, that means it would have to come form the JOIN.

In order to come from the join, the invoice_items table would have to be read first. This is where my suggested index comes into play.

Since you are providing the manufacturer_id in the WHERE clause, we start the new index there. Then the invoice_id as the second column is immediately accessible and so can be used to JOIN with the invoice table on your desired id_customer_id index.

Finally, by adding revenue to the end of my suggested index, you allow the SELECT clause to have easy access to that value, and so avoid a double lookup, which would also discourage the optimizer from choosing these indexes, if we didn't have that column.

As it is, without an index like I suggested the optimizer would have to do a a lot more work on the invoice_items table in order to get both the invoice_id and revenue columns for your given manufacturer_id.

Note that the single column index on manufacturer_id will be of little help in this situation, as although it allows quickly finding the primary key values for that manufacturuer_id, it must then perform a secondary lookup for the invoice_id and revenue, which is likely why it doesn't bother with that route.

Even worse, the (invoice_id,manufacturer_id) isn't useful either, as the invoice_id is first, which means to be useful we need to already have the invoice_id... but your index of id_customer_id in the invoice table also starts with that value, and so this index doesn't provide any benefit to the optimizer in terms of starting with it in order to JOIN to your desired index.

Hopefully that provides you with a good idea of why the optimizer is ignoring your efforts to use that particular index. Which, by the way, is a very good index to have, when combined with my suggested index.

  • This was extremely useful, did not think that the order of indexes does matter that much. I've added the suggested index, also recreated one for invoice table and from 36k rows went down to 4k. – Szabi Zsoldos Dec 4 '18 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.