0

I have three table with more than 1 millions of rows and one of my query take more than 30 seconds with a date range of 3 months. I have tried multiples index and I think I have respect index orders. I don't know what to do go get a better performance.

SELECT
MAX(table2.id) as item_id,
MAX(table2.sampled_inspected) as sample_size,
table1.group_col AS ass_group_id

FROM

table1
INNER JOIN table2  ON table1.id = table2.assignment_id
LEFT OUTER JOIN table3  ON table1.po_id = table3.id
LEFT JOIN table4  ON table4.id = table3.supplier_id
LEFT JOIN table5  ON table5.id = table3.project_id

WHERE table1.recycled=0
    AND table2.inspection_status_id > 0
    AND table2.inspection_status_id != 50
    AND table3.importer_id = 215
    AND table5.recycled = 0
    AND table3.project_id IN ( '2062','2063','2064','2065' )
    AND table2.inspection_completed_date >= DATE('2019-10-01')
    AND table2.inspection_completed_date <= DATE('2020-01-29')
GROUP BY ass_group_id

and here my 5 tables

CREATE TABLE `table1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `recycled` int(1) NOT NULL DEFAULT '0',
  `group_col` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `po_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_assignment_po` (`po_id`),
  KEY `recycled` (`recycled`,`group_col`),
  KEY `group_col` (`group_col`)
) ENGINE=InnoDB AUTO_INCREMENT=3324705 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assignment_id` int(10) unsigned DEFAULT NULL,
  `sampling_size` int(11) DEFAULT NULL,
  `notes` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo_report` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inspection_status_id` int(11) DEFAULT '0',
  `inspection_result_id` int(1) NOT NULL DEFAULT '0',
  `inspection_completed_date` datetime DEFAULT NULL,
  `recycled` int(1) NOT NULL DEFAULT '0',
  `qty_to_inspect` decimal(11,2) DEFAULT NULL,
  `defect_rate` decimal(16,13) DEFAULT NULL,
  `sampled_inspected` int(11) unsigned DEFAULT NULL,
  `inspection_report_id` int(11) unsigned DEFAULT NULL,
  `poline_id` int(11) unsigned DEFAULT NULL,
  `item_status_id` int(11) NOT NULL DEFAULT '0',
  `production_line` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_wsxix_assignments` (`assignment_id`),
  KEY `index_inspection_report_reportid` (`inspection_report_id`),
  KEY `index_ai_inspection_completed_date` (`inspection_completed_date`),
  KEY `index_item_poline` (`poline_id`),
  KEY `index_item_poline_assignment` (`poline_id`,`assignment_id`),
  KEY `wsxix_assignments_items_inspection_status_id` (`inspection_status_id`),
  KEY `wsxix_assignments_items_inspection_result_id` (`inspection_result_id`),
  KEY `inspection_completed_date` (`inspection_completed_date`,`inspection_result_id`,`inspection_status_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3324705 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `table3` (
                          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

                          `buyer_id` int(11) unsigned DEFAULT NULL,
                          `client_id` int(11) unsigned DEFAULT NULL,
                          `merchandiser_user_id` int(11) unsigned DEFAULT NULL,
                          `factory_id` int(11) unsigned DEFAULT NULL,
                          `dc_id` int(11) unsigned DEFAULT NULL,
                          `parent_id` int(10) unsigned DEFAULT NULL,
                          `supplier_id` int(11) unsigned NOT NULL,
                          `importer_id` int(11) unsigned NOT NULL,
                          `project_id` int(11) unsigned NOT NULL,
                          `tier_two_id` int(11) unsigned DEFAULT NULL,
                          `store_id` int(11) unsigned DEFAULT NULL,
                          PRIMARY KEY (`id`),
                          KEY `buyer_id` (`buyer_id`),
                          KEY `client_id` (`client_id`),
                          KEY `merchandiser_user_id` (`merchandiser_user_id`),
                          KEY `FK_po_factory_companies_id` (`factory_id`),
                          KEY `FK_po_dc_companies_id` (`dc_id`),
                          KEY `index_po_parentid` (`parent_id`),
                          KEY `index_purchaseorders_cie` (`supplier_id`),
                          KEY `index_poimporter_cie` (`importer_id`),
                          KEY `index_purchaseorders_project` (`project_id`),
                          KEY `facory_importer_id` (`factory_id`,`importer_id`),
                          KEY `posu` (`project_id`,`supplier_id`),
                          KEY `store_id` (`store_id`),
                          KEY `tier_two_id` (`tier_two_id`)

) ENGINE=InnoDB AUTO_INCREMENT=34959 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `table4` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_company_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `parent_company_id` (`parent_company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=272 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `table5` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `recycled` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `recycled` (`recycled`)
) ENGINE=InnoDB AUTO_INCREMENT=2066 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE: Made change from @Rick Martin comments

Here my Explain result avec your change enter image description here

My table size:

Table1: 3.3 Millions

Table2: 3.5 Millions

Table3: 375

Table4 ( Not used anymore and removed in my query): 57

Table5: 5

@Willem Renzema

enter image description here

  • 1) Remove table4 from FROM clause - it is not used and do not effect on the result. 2) Replace all LEFT JOIN with INNER JOIN which they are in practice. – Akina Jan 29 at 20:03
  • No gain here with your recommended changes, I updated my sql request, I forgot to put the ass_group_id in my query – Kouja Jan 29 at 20:18
  • I forgot to put the ass_group_id in my query Specify what table it is from. And it is strange when GROUP BY field is not included into output... you will not know which ass_group_id each output record is referenced to. – Akina Jan 29 at 20:20
  • @Akina , from table1, ass_group_id is include in my select fields list – Kouja Jan 29 at 20:25
  • 1
    @Kouja There is a logical error in your query. table2.defect_rate cannot be deterministically selected, as multiple values can be present for each GROUP BYd row. It is important to understand that the value returned here is NOT necessarily the value that is in the row that is returned for the sample_size value. In fact, you should assume that it isn't. You know your data, so it is possible (although reflects bad table design) that every defect_rate for any given group_col is identical. Is that the case? If not, the query should be fixed before trying to optimize. – Willem Renzema Jan 29 at 21:01
0

In addition to changes you already made based on other answers and comments, I would add the following 2 indexes:

ALTER TABLE table1
ADD INDEX dbawr1 (recycled,group_col,po_id)

ALTER TABLE table2
ADD INDEX dbawr2 (assignment_id,inspection_completed_date,sampled_inspected)

Trying to get a MAX value for two different columns at the same time, from the same table, is unfortunately going to be something that is more difficult to optimize.

With these indexes it should hopefully try to do table1 first, and so potentially avoid the filesort, which is likely much of the performance problem.

Provide the EXPLAIN output after these changes, and how quickly the query itself runs. We'll see if this made progress before I suggest more changes.

Forcing table1 to be first

Well, it appears that the query optimizer is refusing to try running the query with table1 being the first table. I believe having table1 go first may be more efficient, so here's your query with it modified to accomplish that goal. Basically, I replaced the LEFT JOINs with STRAIGHT_JOINs.

SELECT
MAX(table2.id) as item_id,
MAX(table2.sampled_inspected) as sample_size,
table1.group_col AS ass_group_id

FROM

table1
STRAIGHT_JOIN table2  ON table1.id = table2.assignment_id
STRAIGHT_JOIN table3  ON table1.po_id = table3.id
STRAIGHT_JOIN table5  ON table5.id = table3.project_id

WHERE table1.recycled=0
    AND table2.inspection_status_id > 0
    AND table2.inspection_status_id != 50
    AND table3.importer_id = 215
    AND table5.recycled = 0
    AND table3.project_id IN ( '2062','2063','2064','2065' )
    AND table2.inspection_completed_date >= DATE('2019-10-01')
    AND table2.inspection_completed_date <= DATE('2020-01-29')
GROUP BY ass_group_id

This query is equivalent logically because all of your LEFT JOINs were already being converted into INNER JOINs already, because you have WHERE clauses that reference those tables. A STRAIGHT_JOIN is the same as an INNER JOIN, but just forces the tables to be joined in specific order.

Run that and let me know how it performs. Also, try this query like you did before, by doing just one of the MAX statements at a time. See if either of those are better.

Last of the ideas

The only other thing I can think of to help, and this may be why the optimizer doesn't want to do table1 first, is that the column you are grouping by is 100 bytes wide.

Is varchar(25) with utf8mb4 charset required? That charset is a good default, but it is 4 bytes wide, and if you are only holding letters and numbers you can reduce that by a factor of 4 by switching to something like the latin1 charset, which is only 1 byte wide. Also, if it was possible to switch that column to be a INT that would be even better, as that is 4 bytes, which is up to 25 times smaller than what you currently have. I don't expect that is practical though.

Warning: Be careful about changing the charset on your production data, as if the charset does matter you'll incur data loss upon conversion.

Either way, definitely try my modified query before you bother with this column.

  • message updated with my explain. With your index, query take 21 seconds to run – Kouja Feb 3 at 15:27
  • @Kouja Hmm. The explain says it isn't even using my index through. Weird. May be random variations in the query time. It is also still using table2 first. Ok, I'll ponder this and see what other ideas I can come up with. There are still a few tricks to try, I believe. – Willem Renzema Feb 3 at 15:37
  • @Kouja Can you try running this as 2 separate queries. One with MAX(table2.id) and ass_group_id and the other with MAX(table2.sampled_inspected) and ass_group_id. Let me know how quickly each of those runs. That will help narrow down if it is the SELECT where I should be focusing my attention. – Willem Renzema Feb 4 at 20:31
  • --- MAX(table2.id) and ass_group_id : 20.6 sec ---------- -------MAX(table2.sampled_inspected) and ass_group_id: 21 sec---- – Kouja Feb 4 at 20:38
  • MAX don't have a big impact, if I remove them still get ~20 seconds. GROUP BY ass_group_id slow all the query – Kouja Feb 4 at 20:40
0
  • Remove the unnecessary LEFT from LEFT JOIN table3.

  • Add index to table3: INDEX(importer_id, product_id, supplier_id, id); remove the then redundant INDEX(importer_id)

  • This is totally unused, remove it. (Note that the Optimizer may not be smart enough to remove it for you.) LEFT JOIN table4 ON table4.id = table3.supplier_id

  • Say NOT NULL on columns that can never be NULL.

  • DATE('2019-10-01') --> '2019-10-01'; that is, the DATE() function is not needed around date-like literals.

  • Beware! In MAX(table2.id) as item_id, MAX(table2.sampled_inspected) as sample_size, the item_id may not correspond to that sample_size; each MAX is computed independently.

  • Please provide table sizes.

  • Please provide EXPLAIN SELECT ... -- It may point out other things that need to be changed.

  • For table1: Replace the index on po_id with INDEX(po_id, recycled, group_col, id).

  • If the two MAXs can be done before JOINing, that would help.

(Some of my advice is cosmetic; some is important to performance; some has other purposes.)

  • RIck James , status updated, explain picture included. MAX() don't have impact in performence, if I remove them it take the same time. If I remove the GROUP BY it take 300MS – Kouja Jan 31 at 13:43

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.