how can improve this query in big mysql database

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply