A project uses an older version of the CodeIgniter[2.1.1].
The query builder is used to retrieve specific data.
The source code is an example like this:
$db
->from('first as A')
->where_in('A.code', $first_values)
->join('second as B', 'A.key_id = B.key_id and B.active', 'inner')
->where_in('B.code', $second_values)
->join('third as C', 'A.key_id = C.key_id and A.key2_id = C.key2_id and C.active', 'inner')
->where_in('C.code', $third_values)
->select('COUNT(A.key_id) AS cnt')
->order_by('A.key_id', 'desc')
->order_by('A.key2_id', 'desc')
->order_by('A.key3_id', 'desc')
;
This will generate the following SQL.
SELECT COUNT(A.key_id) as cnt
FROM first AS A
INNER JOIN second AS B ON A.key_id = B.key_id and B.active
INNER JOIN third AS C ON A.key_id = C.key_id and A.key2_id = C.key2_id and C.active
WHERE A.code IN ('someone')
AND B.code IN ('1','2','3')
AND C.code IN ('4','5','6')
ORDER BY A.key_id desc, A.key2_id desc, A.key3_id desc
From here on, the problem is.
The order of the tables to be joined depends on the environment in which this SQL is executed.
And the problem is that the index is no longer used because of the join order.
Perhaps the environment is such that one table is extremely large and the optimizer makes different decisions.
In the range I tried, I think that there is no choice but to specify the join order with STRAIGHT_JOIN.
Therefore, I searched for a way to realize STRAIGHT_JOIN with the query builder of CodeIgniter[2.1.1].
The first thing I thought about was writing in the SELECT part.
->select('STRAIGHT_JOIN COUNT(A.key_id) AS cnt')
I think it's good because the order is still specified and the index is used.
However, although omitted in the example, when joining multiple tables, it seems that the overall order is specified, so it is unclear whether it is optimal or not.
The next idea was to be able to execute a function on a table-by-table basis.
Below is where the file was added.
├── application
│ ├── core
│ │ └── MY_Loader.php // require 'application/database/DB.php'
│ ├── database
│ │ ├── DB_active_rec.php // added straight_join function
│ │ └── DB.php // require 'application/database/DB_active_rec.php'
I think it's okay to add it to the join function, but I created a dedicated function.
public function straight_join($table, $cond)
{
$this->_track_aliases($table);
if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))
{
$match[1] = $this->_protect_identifiers($match[1]);
$match[3] = $this->_protect_identifiers($match[3]);
$cond = $match[1].$match[2].$match[3];
}
$join = 'STRAIGHT_JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond;
$this->ar_join[] = $join;
if ($this->ar_caching === TRUE)
{
$this->ar_cache_join[] = $join;
$this->ar_cache_exists[] = 'join';
}
return $this;
}
Then change the join of a table to STRAIGHT_JOIN.
->straight_join('second as B', 'A.key_id = B.key_id and B.active')
Unfortunately I'm not familiar with CodeIgniter.
At the moment, I think the only way to get the data is to join with STRAIGHT_JOIN.
If the extension method is wrong, or if you have already tried STRAIGHT_JOIN in CodeIgniter, please give me an example or the best way.
This post focuses on how to implement the CodeIgniter, so this is a bit different from the main point in this case, but I'll write it here for reference.
SELECT
A.key_id
FROM
first as A
INNER JOIN
second as B ON A.key_id = B.key_id and B.active
INNER JOIN
third as C ON A.key_id = C.key_id and A.key2_id = C.key2_id and C.active
WHERE
A.code IN ('someone')
In this case, the join order is A, B, C.
A[type:ref,ref:const], B[type:eq_ref,ref:A.key_id], C[type:eq_ref,ref:A.key2_id]
SELECT
A.key_id
FROM
first as A
INNER JOIN
second as B ON A.key_id = B.key_id and B.active
INNER JOIN
third as C ON A.key_id = C.key_id and A.key2_id = C.key2_id and C.active
WHERE
A.code IN ('someone')
AND
B.code IN ('1','2','3')
However, adding the WHERE clause changes the order, and it seems that the index is not used and it is a full scan.
In this case, the join order is B, C, A.
B[type:ALL,ref:NULL], C[type:ref,ref:B.key_id], A[type:ref,ref:C.key2_id]
(You can get the same result by using left join instead of inner join.)
For databases in a development environment with the same configuration, the join order is expected[A,B,C]. (Although the number of registered data is small and different.)
