CODEWITHSUNDEEP
mysqlperformancejoin
Albert Sayapov
Albert Sayapov

Reputation: 21

Mysql make 3 queries in 1 and improve performance

I'm trying to create a report and running 4 queries, but performance is so terrible.

I'm using 2 tables

This one has 2500 items in it

CREATE TABLE `bolt_accounts` (
  `id` int(11) NOT NULL,
  `slug` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `datecreated` datetime NOT NULL,
  `datechanged` datetime NOT NULL,
  `datepublish` datetime DEFAULT NULL,
  `datedepublish` datetime DEFAULT NULL,
  `username` varchar(32) COLLATE utf8_unicode_ci DEFAULT '',
  `ownerid` int(11) DEFAULT NULL,
  `status` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `templatefields` longtext COLLATE utf8_unicode_ci COMMENT '(DC2Type:json_array)',
  `managerid` varchar(128) COLLATE utf8_unicode_ci DEFAULT '',
  `parentid` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `name` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `qualify` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `regdate` date DEFAULT NULL,
  `city` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `phone` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `passhash` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `cookie` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `resettoken` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `block` tinyint(1) NOT NULL DEFAULT '0',
  `blocksms` tinyint(1) NOT NULL DEFAULT '0',
  `birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `bolt_accounts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `IDX_9C703491989D9B62` (`slug`),
  ADD KEY `IDX_9C703491AFBA6FD8` (`datecreated`),
  ADD KEY `IDX_9C703491BE74E59A` (`datechanged`),
  ADD KEY `IDX_9C703491A5131421` (`datepublish`),
  ADD KEY `IDX_9C703491B7805520` (`datedepublish`),
  ADD KEY `IDX_9C7034917B00651C` (`status`),
  ADD KEY `IDX_9C703491C13A5CC2` (`managerid`),
  ADD KEY `IDX_9C703491856A684C` (`parentid`(255)),
  ADD KEY `IDX_9C7034911E6AC3AE` (`regdate`),
  ADD KEY `IDX_9C7034914709B432` (`birthday`);

and another one with all statistics, it has more than 1 400 000 items in it

CREATE TABLE `bolt_statistics` (
  `id` int(11) NOT NULL,
  `slug` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `datecreated` datetime NOT NULL,
  `datechanged` datetime NOT NULL,
  `datepublish` datetime DEFAULT NULL,
  `datedepublish` datetime DEFAULT NULL,
  `username` varchar(32) COLLATE utf8_unicode_ci DEFAULT '',
  `ownerid` int(11) DEFAULT NULL,
  `status` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `templatefields` longtext COLLATE utf8_unicode_ci COMMENT '(DC2Type:json_array)',
  `managerid` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `statdate` datetime DEFAULT NULL,
  `lopv` double NOT NULL DEFAULT '0',
  `gope` double NOT NULL DEFAULT '0',
  `gopv` double NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `bolt_statistics`
  ADD PRIMARY KEY (`id`),
  ADD KEY `IDX_BE38DFD2989D9B62` (`slug`),
  ADD KEY `IDX_BE38DFD2AFBA6FD8` (`datecreated`),
  ADD KEY `IDX_BE38DFD2BE74E59A` (`datechanged`),
  ADD KEY `IDX_BE38DFD2A5131421` (`datepublish`),
  ADD KEY `IDX_BE38DFD2B7805520` (`datedepublish`),
  ADD KEY `IDX_BE38DFD27B00651C` (`status`),
  ADD KEY `IDX_BE38DFD2C13A5CC2` (`managerid`(255));

So the problem is, when I join this tables together, performance become low...

SELECT ba.managerid,name,replace(phone,'+','') as phone,passhash, date_format(ba.datepublish,'%d.%m.%Y %H:%i') as datepublish, max(bs.lopv) as lopv, max(bs.gopv) as gopv 
FROM bolt_accounts ba 
LEFT JOIN bolt_statistics bs ON ba.managerid=bs.managerid 
WHERE (parentid='007-645930') 
AND (date(ba.datechanged)=('2018-01-06')) 
AND (date(bs.datecreated)=('2018-01-06')) 
GROUP BY ba.managerid 
ORDER BY gopv desc

this query will run for 360-450ms ~0,3 of a sec.. It will return all managerids that has parentid=007-645930 some thing like that:

managerid
007-663360
007-677590
007-697191
007-1526400
007-1155884
007-1842169
077-1564660
007-1883072
007-777143
007-1865946
007-1875083
007-1753407
007-1322124
007-1100631
007-1603795
007-1171656
007-1890892
007-1166247
007-1564611
007-1882959
007-1145375
007-1878383
007-1128857
007-1762655
007-1346877
007-1714252
007-1709538
007-1319044
007-1698517
007-1316756
007-1679094
007-1298984
007-1905146
007-1675451
007-1287166
007-1899632
007-1629224
007-1190862
007-1894824
007-1616741
007-1171665
007-1894330

Than I take 1 id from that list, and run 3 queries

SELECT max(s.lopv) as lopv, max(s.gopv) as gopv 
FROM bolt_statistics s WHERE (managerid='007-663360') 
AND (datecreated between DATE_FORMAT('2018-01-06' - INTERVAL 1 MONTH,'%Y-%m-28 23:00:00') and DATE_FORMAT(LAST_DAY('2018-01-06' - INTERVAL 1 MONTH),'%Y-%m-%d 23:59:59'))

execution time 20-25ms

SELECT max(s.lopv) as lopv, max(s.gopv) as gopv 
FROM bolt_statistics s 
WHERE (managerid='007-663360') 
AND (date(datecreated) = date('2018-01-06' -INTERVAL 1 day))

execution time 15-20ms

SELECT max(s.lopv) as lopv, max(s.gopv) as gopv 
FROM bolt_statistics s 
WHERE (managerid='007-663360') 
AND (date(datecreated) = date('2018-01-06' -INTERVAL 2 day))

execution time 15-20ms

When all executions are over, it took 1,5 sec (1500ms) to render the php report.

I know, that I'm not quite good at mysql querying ;)) but I wonder, how can I improve performance on that queries?

Will it be much faster if I union all this queries in 1?

Upvotes: 2

Views: 69

Answers (1)

Rick James
Rick James

Reputation: 142540

Do those fields really need a full 256 characters? Change them to a reasonable number, then get rid of the prefixing on ADD KEY IDX_BE38DFD2C13A5CC2 (managerid(255)), etc. (Prefix indexes are often useless.)

Don't hid columns inside functions (date(ba.datechanged)). Instead:

AND ba.datechanged >= '2018-01-06' - INTERVAL 2 DAY
AND ba.datechanged  < '2018-01-06' - INTERVAL 3 DAY

Note: The above pattern works fine regardless of what datatype datechanged is -- DATE, DATETIME, DATETIME(6), TIMESTAMP. And the Optimizer can make use of an index such as ...

After that, have the composite INDEX(managerid, datecreated) for significant performance improvement.

Use a derived table instead of LEFT JOIN plus GROUP BY. This is likely to improve speed a bunch.

What is status? Why VARCHAR(32)? If it is a simple, low-cardinality, value, don't index it by itself; the index won't be used.

(There may be more tips, but this should get you started.)

Upvotes: 1

Related Questions