0

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

3
  • perfiomance questionws need more information please see dba.meta.stackexchange.com/questions/3034/… – nbk Apr 26 at 10:58
  • Create one composite index instead of separate ones. – Akina Apr 26 at 11:00
  • "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. – Rick James Apr 27 at 15:36
0

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

8
  • thank you. your answer is below because too long as a comment: – aynaz Apr 27 at 4:37
  • @aynaz - I see nothing "below"; to what do you refer? – Rick James Apr 27 at 4:43
  • sorry, I have edited my question. please see again that – aynaz Apr 27 at 5:07
  • 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. – Rick James Apr 27 at 15:38
  • added composite index and was very helpful. I hope it wasn't negative effect in other situations – aynaz Apr 27 at 20:30

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.