There is a table and in it 15000 records.
The speed of the query is as much as 0.5 seconds.
How can this be optimized?
Request:

SELECT `cat`.*, (SELECT COUNT(1) FROM `board` WHERE `board`.`podcat` = `cat`.`id` AND `status` = '1' AND `timesrok` > '".intval($realtime)."') AS `count_board` FROM `cat` WHERE `ref` = '".intval($cat['id'])."' ORDER BY `realid` ASC

Table structure

 CREATE TABLE IF NOT EXISTS `board` ( `id` int(11) auto_increment, `id_user` int(11) NOT NULL, `cat` int(11) NOT NULL, `podcat` int(11) NOT NULL, `name` varchar(250) NOT NULL, `name_translate` varchar(500) NOT NULL, `file` varchar(50) NOT NULL, `foto` varchar(50) NOT NULL, `fotka` varchar(50) NOT NULL, `screen` varchar(50) NOT NULL, `size` varchar(50) NOT NULL, `size_convert` varchar(50) NOT NULL, `price` varchar(11) NOT NULL, `telefon` varchar(13) NOT NULL, `email` varchar(32) NOT NULL, `contact` varchar(32) NOT NULL, `send` int(1) NOT NULL default '0', `city` varchar(32) NOT NULL, `skype` varchar(32) NOT NULL, `time` int(11) NOT NULL, `timeupdate` int(11) NOT NULL, `timesrok` int(11) NOT NULL, `top` int(1) NOT NULL default '0', `toptime` int(11) NOT NULL, `srochno` int(1) NOT NULL default '0', `timesrochno` int(11) NOT NULL, `vydelit` int(1) NOT NULL default '0', `timevydelit` int(11) NOT NULL, `torg` int(1) NOT NULL default '0', `by` int(1) NOT NULL default '0', `free` int(1) NOT NULL default '0', `status` int(1) NOT NULL default '2', `convert` int(1) NOT NULL default '2', `type` int(1) NOT NULL default '0', `activation` int(1) NOT NULL default '0', `count` int(11) NOT NULL default '0', `text` varchar(5000) NOT NULL, `prosmotrov` int(11) NOT NULL, `lat` varchar(11) NOT NULL, `lng` varchar(11) NOT NULL, `watermark` int(1) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

And the second

 -- -- Структура таблицы `cat` -- DROP TABLE IF EXISTS `cat`; CREATE TABLE IF NOT EXISTS `cat` ( `id` int(11) auto_increment, `ref` int(11) NOT NULL, `realid` int(11) NOT NULL, `name` varchar(500) NOT NULL, `translate` varchar(500) NOT NULL, `keywords` varchar(5000) NOT NULL, `logo` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
  • EXPLAIN - user6550
  • EXPLAIN joxi.ru/DmBxlQ0uZppKAP - vitagame
  • Not bad to see the structure of the table ( show create table ) to see. Do you have any indices? - BOPOH
  • Added structure to the first post. - vitagame
  • All fields were not needed, it was enough only those that participate in the request. And the second table would also lead, although it is clear that there are no indices. It is necessary at least on the podcat and status hang the index. In the cat table, too, the index on ref would not hurt. But for an accurate answer, one should know the remaining samples and how often they are performed. Therefore, you yourself have to decide where and what to hang the indices. but 0.5s is not so much, especially if the request is not executed often. - BOPOH

0