Describe your bug or feature request.

My orders view is loading very slowly, have about 35k orders, found adding an index to state improves things from 3sec down to a few ms

Propose adding a simple index to the table: alter table commerce_order add index (state);

CommentFileSizeAuthor
#7 3224681-7.patch1.12 KBjsacksick
#4 Screenshot 2021-07-22 at 09.54.23.png134.3 KBdarrenwh

Issue fork commerce-3224681

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

About issue forks

Comments

darrenwh created an issue. See original summary.

darrenwh’s picture

darrenwh commented
Status: Active » Closed (works as designed)

It has a key

darrenwh’s picture

darrenwh commented
Status: Closed (works as designed) » Active

Oops, setting back as active this field does not have an index

darrenwh’s picture

darrenwh commented
StatusFileSize
new Screenshot 2021-07-22 at 09.54.23.png134.3 KB

Example

darrenwh opened merge request !57

darrenwh’s picture

darrenwh commented

Not sure the best way of doing this: 

/**
 * Update state field add an index.
 */
function commerce_order_update_82xx(){
  $spec = [
    'fields' => [
      'state' => [
        'description' => 'The state field.',
        'type' => 'varchar',
        'length' => 255,
        'default' => '',
      ],
    ],
  ];
  $schema = Database::getConnection()->schema();
  $schema->addIndex('commerce_order', 'state', ['state'], $spec);
}

Will update the field, but will need to update the schema too.

Checking out the current branch seems to exclude the work from this ticket https://www.drupal.org/node/3183315

I have tried adding an extra field to the field_indexes list on the schema definition and rerunning the install on commerce_order_update_8210() and it does not seem to add the field?

jsacksick’s picture

jsacksick commented
Status: Active » Needs review
StatusFileSize
new 3224681-7.patch1.12 KB

The attached patch should do the trick.

jsacksick’s picture

jsacksick commented
Status: Needs review » Fixed

Committed, thanks!

  • jsacksick committed a65d732 on 8.x-2.x 
    Issue #3224681 by darrenwh, jsacksick: Add index to state field on...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.