mysql - 此查询的最佳索引是什么 - 如果条件？

Question

我有一个带有多个 if 条件的查询，这意味着只有在有输入或数据时才执行。

下面是查询

        var sql = `SELECT 
        COALESCE(commerce_order.total_price_usd, 0) AS 'Total price (USD)',
        commerce_order.country AS Country,
        commerce_order.customer_id AS 'Customer Id',
        COALESCE(commerce_order.ffm_status, "unfulfilled") AS 'Fulfillment status'
        FROM commerce_order
        WHERE merchant_id = ?`;
        var bind = [p.data.store_id]
        const fromIndex = sql.indexOf('FROM');

        if (p.data.hasOwnProperty('email')){
            sql += " AND commerce_order.customer_email REGEXP ? "
            bind.push(p.data.email);
        };

        if (p.data.hasOwnProperty('startDate')){
            sql += " AND DATE(commerce_order.processed_at) >= ? "
            bind.push(p.data.startDate);
        };

        if (p.data.hasOwnProperty('endDate')){
            sql += " AND DATE(commerce_order.processed_at) <= ? "
            bind.push(p.data.endDate);
        };

        if (p.data.hasOwnProperty('paymentStatus')){
            if (p.data.paymentStatus !== 'all'){
                sql += " AND commerce_order.financial_status = ? "
                bind.push(p.data.paymentStatus);
            };
        };

        if (p.data.hasOwnProperty('fulfillmentStatus')){
            if (p.data.fulfillmentStatus !== 'all'){
                if (p.data.fulfillmentStatus === 'fulfilled'){
                    sql += " AND commerce_order.ffm_status = ? ";
                    bind.push(p.data.fulfillmentStatus);
                } else if (p.data.fulfillmentStatus === 'unfulfilled'){
                    sql += " AND commerce_order.ffm_status IS NULL "
                };
            };
        };

        if (p.data.hasOwnProperty('order_number')){
            sql += " AND commerce_order.order_number = ? "
            bind.push(p.data.order_number);
        };

        sql += " ORDER BY commerce_order.processed_at "

如何为此查询创建复合索引？还是我只需要单列索引？请指教，谢谢。

标签： mysql