mysql - What is the best optimization for this table and its queries? -

i have table:

create table if not exists `listings` (   `id` int(10) unsigned not null auto_increment,   `type` tinyint(1) not null default '1',   `hash` char(32) not null,   `source_id` int(10) unsigned not null,   `link` varchar(255) not null,   `short_link` varchar(255) not null,   `cat_id` mediumint(5) not null,   `title` mediumtext not null,   `description` mediumtext,   `content` mediumtext,   `images` mediumtext,   `videos` mediumtext,   `views` int(10) not null,   `comments` int(11) not null default '0',   `comments_update` int(11) not null default '0',   `editor_id` int(11) not null default '0',   `auther_name` varchar(255) default null,   `createdby_id` int(10) not null,   `createdon` int(20) not null,   `editedby_id` int(10) not null,   `editedon` int(20) not null,   `deleted` tinyint(1) not null,   `deletedon` int(20) not null,   `deletedby_id` int(10) not null,   `deletedfor` varchar(255) not null,   `published` tinyint(1) not null default '1',   `publishedon` int(20) not null,   `publishedby_id` int(10) not null,   primary key (`id`),   key `hash` (`hash`) ) engine=myisam  default charset=utf8 row_format=compact auto_increment=91628 ; 

and bad queries this

select  id,type , source_id, link, short_link, cat_id, title,         description, images, views, comments, published, publishedon,         content, comments_update, editor_id, auther_name, createdby_id,         createdon, editedby_id, editedon, deleted, deletedon,         deletedby_id, deletedfor, publishedby_id      listings      (cat_id in ( 98 ))       ,  (type in ('1'))       ,  (source_id in ('78'))and (from_unixtime( publishedon ) >= subdate( now( ) ,                         1 )            )       ,  (deleted =0)       ,  (published =1)     order  `publishedon` desc     limit  10 offset 0  

and

select  id,type,source_id,link,short_link,cat_id,title,description,        images,views,comments,published,publishedon      listings      (title rlike 'اليمن|عدن')       ,  (id != 89024)       ,  (deleted = 0)       ,  (published = 1)     order  publishedon desc     limit  6 offset 0  

and

select min(id) listings     (id > 91152) , (cat_id = '134') 

and

select  count(id)      listings      (publishedon >= '1442963362'               ,  publishedon <= '1443568162'            )       ,  (cat_id in ('19', '20', '21', '22', '23', '24', '27',                         '32', '35', '110', '54', '38', '39', '41', '42', '43',                         '44', '45', '46', '47', '49', '56', '57', '51', '55',                         '58', '59', '60', '61', '62', '102', '95', '96', '98',                         '101', '103', '104', '105', '106', '124', '125', '130',                         '131', '132', '133', '134', '135')            )  

this query may takes 0.4 sec done. in 1 page may contain 5 queries this. big problem; causes server load , down time.

this query

select * `listings` id = 5455 

takes 0.0004 sec done because depend on index of pk

how can make indexes columns in first query?

and many times when use "show processlist", see "waiting table level lock" , "sorting data".

the application insert/update many rows times; how can solve this?

your query basically:

select . . . listings l cat_id = 98 ,       type = 1 ,       source_id = 78 ,       deleted = 0 ,       published = 1 ,       from_unixtime( publishedon ) >= subdate( now( ) , 1 ) order `publishedon` desc limit 10 offset 0  

for performance, start composite index on (cat_id, type, source_id, deleted, published, publishedon). syntax is:

create index idx_listings_6 on listings(cat_id, type, source_id, deleted, published, publishedon desc); 

next, suggest re-writing where clause as:

select . . . listings l cat_id = 98 ,       type = 1 ,       source_id = 78 ,       deleted = 0 ,       published = 1 ,       publishedon >= unix_timestamp(subdate( now( ) , 1 )) order `publishedon` desc limit 10 offset 0  

and use same index above.