Yii Framework - InnoDB vs MyISAM

Question

i have a question: i have built a big application with Yii and InnoDB and came to the problem, that the insert/update durate really really long time, here is my php report:

INNODB: admin User update 55.247464895248 seconds ekuskov User update 13.282548904419 seconds doriwall User update 0.002094030380249 seconds

MYISAM: admin User update 7.8317859172821 seconds ekuskov User update 1.6304929256439 seconds doriwall User update 0.0020859241485596 seconds

Can anyone suggest some solution to speed up the insert/update?

EDIT ----------------------------------------------

Now i used some very simple insert loop:

public function run($args) {
    $time = -microtime(true);

    $begin    = DateTime::createFromFormat('Y-m-d H:i:s', '2010-01-01 00:00:00');
    $end    = DateTime::createFromFormat('Y-m-d H:i:s', '2013-01-01 00:00:00');
    $end->add(new DateInterval('P1D'));
    $interval = DateInterval::createFromDateString('1 day');
    $days     = new DatePeriod($begin, $interval, $end);


    foreach ( $days as $day ) {
        echo "i";
        $track = new TimeTracking();
        $track->user_id = 25;
        $track->date = $day->format('Y-m-d H:i:s');
        $track->active = 4;
        $track->save(false);
    }

    $time += microtime(true);
    echo count($days)." items insert - $time seconds\n";
}

and now the INSERT times are following:

InnoDB: items insert - 72.269570827484 seconds

MyISAM: items insert - 0.87537479400635 seconds

[EDIT] And now i was counting time for whole SAVE method and Yii Models "save()" function:

UPDATE: model->save(false) - 0.1096498966217 seconds

UPDATE: controller save function () - 0.1302649974823 seconds

CREATE: model->save(false) - 0.052282094955444 seconds

CREATE: controller save function () - 0.057214975357056 seconds

Why just save() method takes so long?

[EDIT] I have tested save() vs command() and they durate same:

$track->save(false);

or

$command = Yii::app()->db->createCommand();
            $command->insert('timeTracking', array(
                    'id'=>NULL,
                    'date'=>$track->date,
                    'active'=>$track->active,
                    'user_id'=>$track->user_id,
            ));

EDIT -----------------------------

And here is a statistic for inserting 1,097 Objects:

save(): 0.86-0.94, 
$command->insert(): 0.67-0.72,
$command->execute(): 0.46-0.48,
mysql_query(): 0.33-0.36

FINALLY ANSWER: If you want to use some massive INSERT or UPDATE methods you should consider to create the functions with direct MYSQL Calls, there you will save almost 70% of execution time.

Regards,

Edgar

Answer 1

A table crawling on insert and update may indicate that you've got a bit carried away with your indexes. Remember that the DB has to stop and recompile indexes after every commit.