Huge DB index size for AWS – Activating plugin makes site unresponsive

Hello,

You are right that the plugin stores many data inside its index table and some of this data can be not required depending on your search options.

So in the next plugin release I plan to add an option to choose what products data to store inside the index table.

For now you can remove form index some data by using following code

add_filter('aws_indexed_data', 'my_aws_indexed_data');
function my_aws_indexed_data( $data ) {
    foreach ( $data['terms'] as $source => $all_terms ) {
        if ( strpos( $source, 'meta_' ) === 0 || strpos( $source, 'tax_' ) === 0 || strpos( $source, 'attr_' ) === 0  ) {
            unset( $data['terms'][$source] );
        }
    }
    return $data;
}

This code removes custom fields, taxonomies and attributes of products from the index table. If you need sources to remove or want to leave some of them – please write to me.

Also, if you don’t need to search for products variations that use this code too

add_filter('aws_indexed_data', 'my_aws_indexed_data2');
function my_aws_indexed_data2( $data ) {
    if ( $data['type'] === 'child' ) {
        return false;
    }
    return $data;
}

This can heavily increase search speed and decrease database load.

After adding this code please re-index plugin table.

Regards

The code needs to go in functions.php?

We only need the search to index “Title” “SKU” and “Tag” – can you modify the code so this will be reflected?

• This reply was modified 4 years, 1 month ago by move7mil.

Please use following code snippet. It will leave inside plugins index table only sources that you mention.

add_filter('aws_indexed_data', 'my_aws_indexed_data');
function my_aws_indexed_data( $data ) {
    $new_data = array();
    foreach ( $data['terms'] as $source => $all_terms ) {
        if ( strpos( $source, 'sku' ) === 0 || strpos( $source, 'title' ) === 0 || strpos( $source, 'tag' ) === 0 ) {
            $new_data[$source] = $all_terms;
        }
    }
    $data['terms'] = $new_data;
    return $data;
}

Also, after adding this code, you need to re-index the plugin table.

Thank you very much for this.

The index is now below 10 Mib! That’s insane savings in size and gains in responsiveness – and makes it usable for shops with large catalogues.

Yes, you are right. That’s why I am planning to add option to control what sources to add inside the plugin index table.

• This reply was modified 4 years, 1 month ago by ILLID.

Is this feature released ?
Because we are also facing similar issue wp_aws_index table is loaded with lots of unused rows.
https://prnt.sc/vgr6tt

@hiecor

Sorry, not yet.

For now if you don’t need to search for the product meta fields you can remove them from the plugin index table. This will decrease table size significantly.
Please use code

add_filter('aws_indexed_data', 'my_aws_indexed_data');
function my_aws_indexed_data( $data ) {
    foreach ( $data['terms'] as $source => $all_terms ) {
        if ( strpos( $source, 'meta_' ) === 0  ) {
            unset( $data['terms'][$source] );
        }
    }
    return $data;
}

After adding this code please re-index plugin table.

Also – if you don’t need to search for product variations you can also remove this content by using following code

add_filter('aws_indexed_data', 'my_aws_indexed_data2');
function my_aws_indexed_data2( $data ) {
    if ( $data['type'] === 'child' ) {
        return false;
    }
    return $data;
}

Hi,
seen that I have the same problem.
Over 155Gb for table wp_aws_index.
I follow this instructions, but I can’t reindex from aws options page because I receive error 500 …
Is there a solution for reindex manually?
May I empty the table deleting all records?

Yes, if you empty the table, and re-index it should work.