This forum has moved to a new location and is in read-only mode. Please visit talk.octobercms.com to access the new location.
I Have a big problem with october. it's slow mysql rendeing! I have 5 table and each one have more than a million records. server ram is 32 GB and Xeon CPU with SSD hard + storage is (ramdisk) one of forms contain 5 partial which load records (with pivot data). everythings work as well but benchmark show loading time is 25 Sec for editing each content!
what we should to do at this situation?
It's so critical anyone know how we should solve this?
if I didn't found any solution I should remove october and develop code again! the problem is from relation!
There is probably a bottleneck somewhere, check the MySQL Slow Query log. If you post the query here we can probably see what is happening.
I have following tables:
Primary: movies, genres, rates
Relation with pivot: movie_genre, movie_rates
each pivot have types in another table: rate_type ,movie_type
and relation is:
public $belongsToMany = [ 'genres' => [ 'Me\Genres\Models\MovieGenre' , 'table' => 'movie_genres' , 'pivot' => [ 'rate' , 'status' ] ] , 'rates' => [ 'Me\Movies\Models\Rate', 'table' => 'movie_rates', 'pivot' => [ 'rate' , 'rate_id', 'status' ] ]
Debugbar show following message and this query take 12 second:
select `rates`.*, `movie_rates`.`movie_id` as `pivot_movie_id`, `movie_rates`.`rate_id` as `pivot_rate_id`, `movie_rates`.`rate` as `pivot_rate`, `movie_rates`.`status` as `pivot_status` from `rates` left join `movie_rates` on `rates`.`id` = `movie_rates`.`rate_id` where (`movie_rates`.`movie_id` = '6141304' or exists (select * from `deferred_bindings` where slave_id = rates.id and `master_field` = 'rates' and `master_type` = 'Me\Movies\Models\Movie' and `session_key` = 'kMSdRPIynUeq70JpeoKdFDEvYL6fGq6GYk6tiKtu' and `is_bind` = '1')) and not exists (select * from `deferred_bindings` where slave_id = rates.id and `master_field` = 'rates' and `master_type` = 'Me\Movies\Models\Movie' and `session_key` = 'kMSdRPIynUeq70JpeoKdFDEvYL6fGq6GYk6tiKtu' and `is_bind` = '0' and id > ifnull((select max(id) from deferred_bindings where
slave_id = rates.id and
master_field = 'rates' and
master_type = 'Me\Movies\Models\Movie' and
session_key = 'kMSdRPIynUeq70JpeoKdFDEvYL6fGq6GYk6tiKtu' and
is_bind = '1'
), 0)) order by `name` desc
movies table contain: 1.8 Million Records, movie_rates: 6 million, movie_genre 4.5 million, movie_type: 20 records, rates 6 records
1-5 of 5