how can improve this query in big mysql database

Question

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

• nbk: perfiomance questionws need more information please see dba.meta.stackexchange.com/questions/3034/…
• Akina: Create one composite index instead of separate ones.
• Rick James: "my query run in 11 seconds. with count(*) or with count(test.code) or without limit 1 not difference" -- The suggestions were more about SQL coding; they rarely impact performance.

Answer 1

"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"?

• aynaz: thank you. your answer is below because too long as a comment:
• Rick James: @aynaz - I see nothing "below"; to what do you refer?
• aynaz: sorry, I have edited my question. please see again that
• Rick James: Adding the composite index should help performance a lot. If it does not, please provide a new EXPLAIN so we can check whether it is actually being used. Meanwhile, I added a few tips on the schema.
• aynaz: added composite index and was very helpful. I hope it wasn't negative effect in other situations