1

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 */;

darkhast_follower log_follow

  • 1
    Welcome to DBA.SE! Could you post the DDL for these tables & indexes please? DDL is always better than images as it helps us recreate the issue you are having. Thanks! – Mr.Brownstone Feb 24 at 10:15
  • @Mr.Brownstone What is DDL? – Marvan Eimaz Feb 24 at 10:18
  • DDL stands for Data Definition Language. Essentially, the CREATE TABLE & CREATE INDEX statements. – Mr.Brownstone Feb 24 at 10:19
  • 1
    @Mr.Brownstone added – Marvan Eimaz Feb 24 at 10:32
  • Please add the output of EXPLAIN SELECT ... for your query. And how much time it takes on average. " performance seems to be slow": is it 10 ms slow or 10 min slow? – ypercubeᵀᴹ Feb 24 at 14:36
0

The query seems ok (although the LIMIT 1 inside the NOT EXISTS subquery is not needed) but you are probably missing some index. I suggest:

  • add a composite index on log_follow (id, other_id):

    ALTER TABLE log_follow
      ADD INDEX id_other_id_idx
        (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.
  • latin charset like? both column indexed log_follow (id, other_id) – Marvan Eimaz Feb 24 at 15:34
  • I suggest a single composite index on both columns. You have 2 indexes, on each column separately. – ypercubeᵀᴹ Feb 24 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.