php - Mysql query with many join's takes long time to load -

i using query record

select * (`content` c) left join `content_assets` ca on `ca`.`contentid` = `c`.`id` left join `assets` on `a`.`act_id` = `ca`.`actorid` inner join `content_gens` cg on `cg`.`contentid` = `c`.`id` inner join `gens` g on `cg`.`genid` = `g`.`gen_id` left join `live_data` l on `l`.`contentid` = `c`.`id` left join `live_type` lt on `lt`.`lt_id` = `l`.`live_type` left join `returned` r on `r`.`content_id` = `c`.`id` `c`.`id` =  '14175' 

my live_data table main issue here causing load time, contains 200k records , taking 20-40 seconds load page. if dont join table fine. using file based caching cache result above query know if above query can optimized more.

edit: table indexes can seen here.

-- phpmyadmin sql dump -- version 3.4.5 -- http://www.phpmyadmin.net -- -- host: localhost -- generation time: apr 08, 2013 @ 11:07 pm -- server version: 5.5.16 -- php version: 5.3.8  set sql_mode="no_auto_value_on_zero"; set time_zone = "+00:00";  -- -- database: `database` --  -- --------------------------------------------------------  -- -- table structure table `assets` --  create table if not exists `assets` (   `act_id` int(11) not null auto_increment, ......   primary key (`act_id`) ) engine=myisam  default charset=utf8 auto_increment=8726 ;  -- --------------------------------------------------------  -- -- table structure table `content` --  create table if not exists `content` (   `id` int(11) not null auto_increment, .........   primary key (`id`) ) engine=myisam  default charset=utf8 auto_increment=14267 ;  -- --------------------------------------------------------  -- -- table structure table `content_asset` --  create table if not exists `content_asset` (   `contentid` int(11) not null,   `actorid` int(11) not null ) engine=myisam default charset=utf8;  -- --------------------------------------------------------  -- -- table structure table `content_gener` --  create table if not exists `content_gener` (   `contentid` int(11) not null,   `genid` int(11) not null ) engine=myisam default charset=utf8;  -- --------------------------------------------------------  -- -- table structure table `gens` --  create table if not exists `gens` (   `gen_id` int(11) not null auto_increment, ......   primary key (`gen_id`) ) engine=myisam  default charset=utf8 auto_increment=65 ;  -- --------------------------------------------------------  -- -- table structure table `live_data` --  create table if not exists `live_data` (   `link_id` int(11) not null auto_increment, .....   primary key (`link_id`) ) engine=myisam  default charset=utf8 auto_increment=214014 ;  -- --------------------------------------------------------  -- -- table structure table `live_type` --  create table if not exists `live_type` (   `lt_id` int(11) not null auto_increment, ....   primary key (`lt_id`) ) engine=myisam  default charset=utf8 auto_increment=4 ;  -- --------------------------------------------------------  -- -- table structure table `returned` --  create table if not exists `returned` (   `r_id` int(11) not null auto_increment, ....   primary key (`r_id`) ) engine=myisam default charset=utf8 auto_increment=1 ; 

and explain query result:

thanks help.

you should add indexes on following columns:

• live_data.contentid
• content_asset.contentid
• content_gener.contentid

and convert big tables innodb using alter table table_name engine = innodb.

also idea use foreign keys.