Mysql – how can improve this query in big thesql database

explainMySQLselect

my query is simple but 10 seconds long to respond. please check and help me to improve that

SELECT 
    COUNT(test.code) AS sumid
FROM
    test
WHERE
    test.state = '03' AND test.type = '0'
        AND test.year = '2020'
        AND test.active IN (1 , 2)
        AND test.status IN (1 , 4)
        AND test.marhale = 0
LIMIT 1

explain that show:

id  select_type   table  partitions   type      possible_keys                         key                       key_len     ref     rows    filtered          Extra  
1   SIMPLE        test              index_merge type,year,marhale,state,status,active   year,state,marhale,type 2,6,2,1     20354   27.24                Using intersect(year,state,marhale,type); Using where

this table is my important table in big database and we have many reports for all of the columns of that this causes us index many of fields.

this show create table of test :

CREATE TABLE `tashilat` (
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `eid` varchar(10) COLLATE utf8_persian_ci NOT NULL,
 `hcode` char(15) COLLATE utf8_persian_ci NOT NULL,
 `code` char(16) COLLATE utf8_persian_ci NOT NULL,
 `listcode` varchar(12) COLLATE utf8_persian_ci DEFAULT NULL,
 `year` smallint(4) NOT NULL DEFAULT '1392',
 `ddate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `type` tinyint(4) NOT NULL,
 `type_level` tinyint(4) NOT NULL,
 `baseprice` varchar(10) COLLATE utf8_persian_ci NOT NULL,
 `prices` varchar(10) COLLATE utf8_persian_ci NOT NULL,
 `marhale` smallint(3) NOT NULL,
 `maxmarhale` smallint(3) NOT NULL DEFAULT '0',
 `paystatus` tinyint(1) NOT NULL DEFAULT '0',
 `marhale_level` smallint(3) NOT NULL DEFAULT '0',
 `pdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `paymethod` smallint(6) DEFAULT NULL,
 `state` char(2) COLLATE utf8_persian_ci NOT NULL,
 `bank` varchar(10) COLLATE utf8_persian_ci NOT NULL,
 `branch` varchar(10) COLLATE utf8_persian_ci NOT NULL,
 `tdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `daccept` tinyint(1) NOT NULL DEFAULT '1',
 `dcode` varchar(32) COLLATE utf8_persian_ci DEFAULT NULL,
 `mtime` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
 `mshkdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `ghtime` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
 `gh` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
 `submitted` tinyint(1) NOT NULL DEFAULT '0',
 `submittedtype` int(11) DEFAULT NULL,
 `submitteddate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `ztype` varchar(2) COLLATE utf8_persian_ci DEFAULT NULL,
 `bdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `pcode` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `lastp` tinyint(4) DEFAULT '0',
 `tozihat` text COLLATE utf8_persian_ci,
 `ncode` varchar(32) COLLATE utf8_persian_ci DEFAULT NULL,
 `ndate` varchar(32) COLLATE utf8_persian_ci DEFAULT NULL,
 `ccode` varchar(32) COLLATE utf8_persian_ci DEFAULT NULL,
 `cdate` varchar(12) COLLATE utf8_persian_ci DEFAULT NULL,
 `bcode` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `bdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `secindicator` varchar(100) COLLATE utf8_persian_ci DEFAULT NULL,
 `hesab` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
 `stage` varchar(3) COLLATE utf8_persian_ci DEFAULT NULL,
 `loantopic` varchar(50) COLLATE utf8_persian_ci DEFAULT NULL,
 `baz` tinyint(1) DEFAULT NULL,
 `accept` tinyint(4) NOT NULL DEFAULT '1',
 `user` varchar(32) COLLATE utf8_persian_ci NOT NULL,
 `sdate` varchar(32) COLLATE utf8_persian_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1',
 `activetype` tinyint(4) NOT NULL DEFAULT '0',
 `status_fact` int(11) DEFAULT NULL,
 `status_date` varchar(25) COLLATE utf8_persian_ci DEFAULT NULL,
 `active` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `tashilat_idx_bank` (`bank`),
 KEY `tashilat_idx_shobe` (`branch`),
 KEY `submitted` (`submitted`) USING BTREE,
 KEY `listcode` (`listcode`),
 KEY `mshkdate` (`mshkdate`),
 KEY `eid` (`eid`),
 KEY `baseprice` (`baseprice`),
 KEY `code` (`code`),
 KEY `hcode` (`hcode`),
 KEY `marhale_level` (`marhale_level`),
 KEY `type` (`type`),
 KEY `year` (`year`),
 KEY `marhale` (`marhale`),
 KEY `maxmarhale` (`maxmarhale`),
 KEY `state` (`state`),
 KEY `status` (`status`),
 KEY `active` (`active`),
 FULLTEXT KEY `sdate` (`sdate`),
 FULLTEXT KEY `ndate` (`ndate`),
 FULLTEXT KEY `cdate` (`cdate`)
 ) ENGINE=InnoDB AUTO_INCREMENT=11293550 DEFAULT CHARSET=utf8           COLLATE=utf8_persian_ci

my query run in 11 seconds.
with count(*) or with count(test.code) or without limit 1 not diffrence

Best Answer

"intersect" tells me that you have a bunch of 1-column indexes, when you should have an 'composite' index with all those columns. In particular:

INDEX(state, type, year, marhale,   -- any order is OK
      status, active)               -- later; in either order

That is, start with all the columns that are tested with =.

Another issue is with COUNT(test.code). That means "count how many rows have code IS NOT NULL". If you really want "count the number of rows", then say simply COUNT(*).

If you want to test NULLness, then tack code onto the end of my recommended index. That way, it will be a "covering" index, which adds another performance boost. (If you go with COUNT(*), tacking code on the end is harmless but not helpful.)

If you want to discuss further, please provide SHOW CREATE TABLE.

One more thing. If you add my composite index, Drop the index for the first column (probably DROP INDEX 'state').

The LIMIT 1 is useless since SELECT COUNT(..) FROM .. will always give exactly 1 row. (It is also harmless.)

More

FULLTEXT indexing is intended for lengthy text strings, not dates.

DATE, DATETIME, or TIMESTAMP should be used for dates/times. VARCHAR makes it difficult to compare dates.

11 "date" columns -- This seems strange. Maybe "date" does not mean "date"?

Related Question