php - CakePHP强制索引mysql查询-订单限制问题

Question

我正在为 mySQL 数据库开发 API。作为框架，我们使用 cakePHP。在构建 API 之后，我们很高兴，直到我们开始在我们的数据库中添加越来越多的记录。我们发现我们使用的查询使用 filesort() 按时间排序。虽然我们索引了时间。为了强制执行 SQL，我们尝试在 phpmyadmin 中使用 FORCE INDEX，并且效果很好。查询速度从 12s 提升到 0.0056。

剩下的唯一问题是框架，cakePHP 不支持索引。我在网上搜索了一整天，但找不到最终答案。最近的解决方案来自:cakephp force index at model除了我没有让它运作良好。我想我使用的是较新版本的 cakephp (2.5.4)。

现在的查询:

   $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
                ));

我现在真的很无能，有什么想法吗？

解决方案只要选定的表是 Book，我就会更改 DBOsource.php 中的 CAKEphp querybuilder。对更大的项目不负责，但在这里可以解决问题。

 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}"); } 别的 { 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;
    }
}

Answer 1

您可以编写核心查询或使用此链接创建您自己的数据源 http://cakephp.1045679.n5.nabble.com/Using-USE-INDEX-or-FORCE-INDEX-in-Model-gt-find-amp-relationships-td3281552.html#a3300205