Indexes do indeed take HDD/SSD drive space. It’s a fact of SQL life.
As for the still-slow operation you mentioned:
I assume you’re using the search box at the upper right corner of the Orders panel. When I put an email address in there, WooCommerce code runs two SQL queries (according to Query Monitor).
SELECT DISTINCT p1.post_id
FROM wp_postmeta p1
WHERE p1.meta_value LIKE ‘%[email protected]%’
AND p1.meta_key IN (‘_billing_address_index’,
‘_shipping_address_index’,’_billing_last_name’,’_billing_email’)
SELECT order_id
FROM wp_woocommerce_order_items as order_items
WHERE order_item_name LIKE ‘%[email protected]%’
These queries look for the email address ‘[email protected]’ anywhere in the various fields WooCommerce uses. For example, the queries will match any of these the values
[email protected]
[email protected] placed this order.
To: [email protected] From:[email protected]
There’s a performance problem baked into those queries. column LIKE ‘%something’ cannot exploit any index in MySQL or MariaDB. So, this isn’t a problem that can be solved by indexes, sad to say.
It’s possible to imagine a plugin with a solution involving, maybe, a FULLTEXT index. But using an index like that would involve hooking the search queries and writing different SQL.
I wish I had a bigger performance boost for you.