Ygthkb

Currently my query is as below:

select id_request,username_request

  from darkhast_follower

 where darkhast_follower.id_request != '9762952594' 

   AND darkhast_follower.status =0 

   AND NOT exists ( select null

               from log_follow 

               where log_follow.other_id = darkhast_follower.id_request 

               AND log_follow.id= '9762952594' LIMIT 1)

 LIMIT 1

Table log_follow has 30 milion rows and darkhast_follower has 1000 rows respectively.

log_follow is indexed on column (other_id) and (id), darkhast_follower is indexed on column (id_request) and (status).

The performance seems to be slow and looking for expert advice on fine-tuning the query and index suggestion. Currently I have 40k active users.

-- Server version: 5.7.23-log

-- PHP Version: 5.6.31



SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET AUTOCOMMIT = 0;

START TRANSACTION;

SET time_zone = "+00:00";





/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;





-- --------------------------------------------------------



--

-- Table structure for table `darkhast_follower`

--



CREATE TABLE `darkhast_follower` (

  `IDD` int(11) NOT NULL,

  `id` varchar(20) NOT NULL,

  `username` varchar(65) NOT NULL,

  `id_request` varchar(20) NOT NULL,

  `username_request` varchar(65) NOT NULL,

  `token_id` text NOT NULL,

  `darkhasti` int(5) NOT NULL,

  `daryafti` int(5) NOT NULL,

  `date` text NOT NULL,

  `status` tinyint(1) NOT NULL,

  `gsm_sender` tinyint(1) NOT NULL,

  `error_report` tinyint(3) NOT NULL,

  `bot` tinyint(1) NOT NULL,

  `back` tinyint(1) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- 

-- Indexes for dumped tables

--



--

-- Indexes for table `darkhast_follower`

--

ALTER TABLE `darkhast_follower`

  ADD PRIMARY KEY (`IDD`),

  ADD KEY `status` (`status`),

  ADD KEY `id_request` (`id_request`);



--

-- AUTO_INCREMENT for dumped tables 

--



--

-- AUTO_INCREMENT for table `darkhast_follower`

--

ALTER TABLE `darkhast_follower`

  MODIFY `IDD` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;



/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;





SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET AUTOCOMMIT = 0;

START TRANSACTION;

SET time_zone = "+00:00";





/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;



 -- --------------------------------------------------------



--

-- Table structure for table `log_follow`

--



CREATE TABLE `log_follow` (

  `IDD` int(11) NOT NULL,

  `id` varchar(20) NOT NULL,

  `username` varchar(65) NOT NULL,

  `other_user` varchar(65) NOT NULL,

  `other_id` varchar(20) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



--

-- Indexes for dumped tables

--



--

-- Indexes for table `log_follow`

--

ALTER TABLE `log_follow`

  ADD PRIMARY KEY (`IDD`),

  ADD KEY `id` (`id`),

  ADD KEY `other_id` (`other_id`);



--

-- AUTO_INCREMENT for dumped tables

--



--

-- AUTO_INCREMENT for table `log_follow`

--

ALTER TABLE `log_follow`

  MODIFY `IDD` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;



/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

The query seems ok but you are probably missing some index. I suggest:

• add an index on log_follow (id, other_id).


• an index on darkhast_follower (status, id_request) might help as well but with such a small table, probably not much.


• the datatype of the various id columns (id, other_id, id_request) are varchar(20) with charset UTF8. If you only have ASCII characters there, it would be better to use Latin charset. This will lower the column width from 60 (20x3) bytes to 20, and thus reduce the index size, both in disk and memory. If you only store integers in these columns, it would be even better to use BIGINT, reducing the size to 8 bytes.