I'm working on an API for a mySQL database. As framework we use cakePHP. After building the API we were happy until we started to add more and more records in our database. We found out that the query we use used filesort() for the order by time. Although we indexed time. To force SQL we tried to use FORCE INDEX in phpmyadmin and this worked well. The query speed went from 12s to 0.0056.
The only issue remaining is that the framework, cakePHP doesn't support indexation. I searched the internet for this all day but couldn't find the final answer. The nearest solution came from: cakephp force index at model except I didn't get it to work well. I suppose I use a newer version of cakephp (2.5.4).
The query as is now:
$resultSet = $this->Book->find('all',array(
'fields' => array('Book.*'),
'contain' => array(
'GeneralSelectMinimumage'=> array('fields'=> array('age')),
'Combine'=> array(
'Location' => array(
//'fields' => array('name,short_description'),
'LocationSelectAddressid' => array(
'LocationAddress'=>array(
'RegionSelectCity'=>array(
'RegionCity'=>array(
'Region'
)
)
)
),
'LocationSelectFacilityid' => array(
'LocationFacility' => array(
'LocationSelectFacility',
'LocationSelectFacilityAnswer'
)
),
'LocationSelectImagesid' => array(
'GeneralImage'
),
),
'author' => array(
'authorSelectauthortypeid' => array(
'authorSelectType'
),
'authorSelectImageid'=>array('GeneralImage'),
)
),
'BookSelectLocationtypeid' => array(
'BookSelectLocationtype'
),
'BookSelectImageid'=> array(
'GeneralImage'
),
'BookSelectVideoid'=> array(
'GeneralVideo'
),
'BookSelectCategoryid'=> array(
'BookCategory'
),
'BookPrice' => array(
'BookSelectPrice',
'BookSelectMethod'
),
'BookSelectObtainid' => array(
'BookObtain'=>array(
'BookSelectObtain'
)
),
'BookSelectTypeid'=>array(
'BookSelectType'
)
),
'joins' => array(
array(
'table' => 'Book_select_categoryid',
'alias' => 'BookSelectCategoryid',
'type' => 'LEFT',
'conditions' => array('Book.id = BookSelectCategoryid.Bookid')
),
array(
'table' => 'Book_category',
'alias' => 'BookCategory',
'type' => 'LEFT',
'conditions' => array('BookSelectCategoryid.categoryid = BookCategory.id')
),
/*array(
'table' => 'Book_price',
'alias' => 'BookPrice',
'type' => 'LEFT',
'conditions' => array('Book.id = BookPrice.Bookid')
),*/
array(
'table' => 'Book_select_price',
'alias' => 'BookSelectPrice',
'type' => 'LEFT',
'conditions' => array('BookPrice.select_priceid = BookSelectPrice.id')
),
array(
'table' => 'COMBINE',
'alias' => 'Combine',
'type' => 'INNER',
'conditions' => array('Book.id = Combine.Bookid')
),
array(
'table' => 'author',
'alias' => 'author',
'type' => 'LEFT',
'conditions' => array('Combine.authorid = author.id AND author.active=1')
),
array(
'table' => 'author_select_imageid',
'alias' => 'authorSelectImageid',
'type' => 'LEFT',
'conditions' => array('author.id = authorSelectImageid.authorid')
),
array(
'table' => 'general_image',
'alias' => 'GeneralImage',
'type' => 'LEFT',
'conditions' => array('GeneralImage.id = authorSelectImageid.imageid OR GeneralImage.id = LocationSelectImagesid.imagesid ')
),
array(
'table' => 'Book_select_type',
'alias' => 'BookSelectType',
'type' => 'LEFT',
'conditions' => array('BookSelectTypeid.typeid = BookSelectType.id')
),
),
'conditions' => array('Book.time >= NOW()','Book.active'=>1, $filters),
'order'=> array('Book.time asc'),
'limit' => $limit,
'offset' => $offset
));
I'm really clueless at this point, Any idea's?
SOLUTION I changed the CAKEphp querybuilder in DBOsource.php whenever the selected table is Book. Not app-liable for bigger project but does the trick here.
public function renderStatement($type, $data) {
extract($data);
$aliases = null;
switch (strtolower($type)) {
case 'select':
// die('TABLE=' . $table);
if($table == "book"){
return trim("SELECT {$fields} FROM {$table} {$alias} FORCE INDEX(time) {$joins} {$conditions} {$group} {$order} {$limit}");
}
else {
return trim("SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}");
}
case 'create':
return "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
case 'update':
if (!empty($alias)) {
$aliases = "{$this->alias}{$alias} {$joins} ";
}
return trim("UPDATE {$table} {$aliases}SET {$fields} {$conditions}");
case 'delete':
if (!empty($alias)) {
$aliases = "{$this->alias}{$alias} {$joins} ";
}
return trim("DELETE {$alias} FROM {$table} {$aliases}{$conditions}");
case 'schema':
foreach (array('columns', 'indexes', 'tableParameters') as $var) {
if (is_array(${$var})) {
${$var} = "\t" . implode(",\n\t", array_filter(${$var}));
} else {
${$var} = '';
}
}
if (trim($indexes) !== '') {
$columns .= ',';
}
return "CREATE TABLE {$table} (\n{$columns}{$indexes}) {$tableParameters};";
case 'alter':
return;
}
}
