davidgeismar / index_hunter Goto Github PK

Retrieve sql logs on server and analyse query usage in order to adapt strategy

IndexOptimiser

find_covering_indexes could be better

• So far the logic of the index_optimiser is pretty simple :

1. take columns in the queries with most columns criteria, sort them by cardinality, create indexes for them.
2. check if these indexes covers the other queries
3. if not create index for these other queries

QueryFinder

Make better regex to match queries

• only handling active record
• doesnt work for chained method when result of query is put into variable
  Ex : transactions = MerchantTransaction.where(status: 1)
transactions.where(kind: 'BATCH_BET') is not caught by regex
• replace_variables_in_query_string could be maybe improved with a config_file and a parser that automatically replaces variables

QueriesDeconstructor

retrieve_query_fields could be better

Right now, to retrieve the column criterias from the queries. I instantiate them as lambdas then call where_values_hash on them.
This method gives info about the query, which is cool.
Hwver where_values_hash only works for active_record method that dont directly call the db ==> not working with all of those :
ACTIVE_RECORD_NOT_SQLABLE = ['find', 'find_by', 'find_by!', 'find_by_sql', 'pluck', 'take', 'exists?', 'any?', 'many?', 'count', 'minimum', 'maximum', 'sum']

I do a dirty hack by transforming temporarily a .find_by into a .where. WE CAN DO BETTER