Mysql – Advice on fine-tuning query and index for performance

index-tuningMySQLoptimizationperformancequery-performance

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

Best Answer

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.
Related Question