Good way to build strong index in MySQL
Quick and Cheap

The quickest method for query review is indeed the slow query log, combined with a tool called pt-query-digest

An example process is to first set long_query_time=1. Set it to 0 for most accurate results, but it might affect IO depending on load of the system.

Let the slow query log run for a period of time, such as 1 week, to capture a significant number of query samples.

Then run pt-query-digest against the slow log.

This tool is free to use.



Continual process

To use a similar process, but have it behind a GUI to know when queries showed up or have been checked, you can set up Anemometer



An enterprise solution

The previous tools mentioned come without much cost, but only focus on queries.

A tool like VividCortex will provide you a great view of the performance of your system, and help you drill down into the queries in many different ways. 

The method VividCortex uses to capture queries is lower impact than enabling the slow query log.