Menu
Is it possible to specify "USE INDEX" or "FORCE INDEX" in CodeIgniter, other than using
$this->db->query()
What I mean is if it's possible to insert "FORCE INDEX" somewhere in one of ActiveRecord's methods.
503
In general, you should create an index on a column in any of the following situations: The column is queried frequently. A referential integrity constraint exists on the column. A UNIQUE key integrity constraint exists on the column.
In case the query optimizer ignores the index, you can use the FORCE INDEX hint to instruct it to use the index instead. In this syntax, you put the FORCE INDEX clause after the FROM clause followed by a list of named indexes that the query optimizer must use.
The FORCE INDEX hint acts like USE INDEX ( index_list ) , with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table. As of MySQL 8.0.
Because MySQL uses only one index for each table in one execution, we need to decide using index for join or group by. Let give GROUP BY a chance and see what happens. According to the explain chart, MySQL will join `answer` table into `user` table.
You can use the from() active record method to add this in queries like this:
$this->db->like('name', 'user', 'after')->from('users use index (name)')->get();
produces sql query like this:
SELECT * FROM (`users` use index (name)) WHERE `name` LIKE 'user%'
One caveat is that the from() method tries to find identifiers and multiple tables so adding one or more , to it's input is most likely to end up as an SQL syntax error.
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
