I have a customer master table with 420K+ rows.
About 30K customers have been active, the rest have been inactive for years.
My user insists on keeping the inactive customers on the master table.
So I added a status column char(1). If the row gets updated, LET status = "A".
Once a month we run, UPDATE customers SET status = "I" WHERE date_last_updated < (today - 365).
Table has: UNIQUE INDEX ON (customer_name); INDEX ON (status,customer_name)
When a customer arrives, a wildcard query is performed on the customer_name char(30) column: *LastName*SurName*First* (EXAMPLE: *RIVERA*RODR*P*) and status = "A".
So I CREATE CLUSTERED INDEX ON customers(date_last_updated desc) to physically
maintain the most recently active customer rows ahead of the inactive rows to improve query times and return less rows, but this actually made the queries
run slower!... Any ideas on how to best optimize this, other than moving inactives to another table?
Another perplexing result is that: I wanted to improve query time on
SELECT MAX(transaction_number) FROM transactions,
so i: CREATE UNIQUE INDEX ON transactions(transaction_number desc)
to quickly retrieve the MAX transaction_number, but it actually retrieves faster when the index is in ascending rather than descending order!!!