ORM[suggestion]

In a large scale application an ORM quickly becomes one of your biggest bottlenecks. It's difficult to optimize indices for queries you aren't writing.

The queries an ORM will use are generated by code, which is, at the very least, predictable. If you understand the ORM's conventions, you should be able to determine its strengths and weaknesses in generating SQL, which will also help you determine when you need to override it and how to apply good indexes to your data. In the very worst case, you can always run some tests and determine what queries are generated.

We can (and should) write indexes based on the needs of the queries we are writing (or our code is writing) in an attempt to maintain the optimal performance of our database. However, it's not truly possible to optimize a database if you aren't making periodic adjustments based on the actual queries run by the database and analysis of how long those queries take to run and how often they are executed. Some databases provide better tools for gathering and analyzing this data than others.

As with any code, you should write SQL (and create tables and indexes) with an eye towards performance, but always beware of premature optimization. Optimization should be based on performance analysis, not prediction, because you could be wasting your time with a micro-optimization while your application is hanging on a query that just needs an additional index which is only required because of the way in which two different parts of the system use the same table.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide