I've currently got a pretty hefty database (for me anyway) with multiple tables exceeding 15 million rows. I'm trying to improve performance on the whole table lookup, I implemented memcached to cache the already parsed result, but the initial lookups are very slow. 20seconds + on a big query.
SELECT `something`, ROUND(SUM( ( `amount` / `something_of_value` ) * `column` ) WHERE ... (the where clause is huge based on many conditionals)
Current setup:
A managed vps server
InnoDB storage for big tables
10.0.36-MariaDB
18gb ram
8 x 2.40 GHz CPU.
we don't use query_cache because 25% of queries are write queries, and after some research if a write/update query is done - the cache is deleted. Is this a correct approach?
Whats the best way to improve the initial lookup without upgrading the hardware? I probably left out a lot of important details, so if you need more info just ask. If you can steer me in a good general direction that would be appreciated also. I know about query optimization, but we are trying to improve the time of lookup and generally mysql performance.
EDIT:
full query:
SELECT `metric`, SUM( `amount` )
FROM big_table
WHERE 1
AND (`metric` = '1' )
AND (`source` = 'some_unique_source'
OR `source` = 'some_other_unique_source'
OR `source` = 'yet_another_unique_source'
OR `source` = 'some_most_unique_source'
OR `source` = 'almost_last_unique_source'
OR `source` = 'the_last_unique_source'
)
AND (`platform` = '2'
OR `platform` = '1'
OR `platform` = '3'
)
AND (`account` = '1'
OR `account` = '2'
OR `account` = '3'
OR `account` = '4'
OR `account` = '5'
OR `account` = '6'
OR `account` = '7'
OR `account` = '8'
OR `account` = '9'
OR `account` = '10'
OR `account` = '11'
OR `account` = '12'
)
AND (`something_id` = 'some.unique.id' )
AND `target_date` >= '2018-08-27'
AND `target_date` <= '2018-08-27'
GROUP BY `metric`;
Create query:
CREATE TABLE `big_table` (
`stat_id` int(8) NOT NULL AUTO_INCREMENT,
`metric` tinyint(1) NOT NULL DEFAULT '0',
`source` varchar(16) DEFAULT NULL ,
`platform` tinyint(1) NOT NULL DEFAULT '0' ,
`account` int(2) DEFAULT NULL ,
`something_id` varchar(128) DEFAULT 'EMPTY SOMETHING_ID',
`target_date` date DEFAULT NULL,
`country` varchar(2) DEFAULT NULL ,
`version` varchar(16) DEFAULT NULL ,
`amount` decimal(16,6) NOT NULL DEFAULT '0.000000' ,
`tax` decimal(12,6) NOT NULL DEFAULT '0.000000' ,
`currency` varchar(3) DEFAULT NULL,
`currency_rate` decimal(12,6) DEFAULT '500.000000',
`rate_updated` int(11) NOT NULL DEFAULT '0',
`multiplier` decimal(16,6) NOT NULL DEFAULT '1.000000',
`unique_key` varchar(180) DEFAULT NULL ,
`caption` varchar(128) DEFAULT NULL,
`transaction_timestamp` timestamp NULL DEFAULT NULL ,
`finalised` tinyint(1) NOT NULL DEFAULT '0',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`stat_id`),
UNIQUE KEY `unique_key` (`unique_key`) USING BTREE,
KEY `target_date` (`target_date`,`currency`),
KEY `index_country` (`country`),
KEY `currency_rate` (`currency_rate`,`multiplier`)
) ENGINE=InnoDB AUTO_INCREMENT=43453089 DEFAULT CHARSET=utf8
Edit:
The date is always 1 day - the script runs a foreach date based on user inputted date range. The returned mysql result is parsed into a multidimensional array and then parsed into a json file after the datarange finishes. Now that I think about it,a better approach may be to make a more intelligent query where the result would be grouped by date, though I don't know how much of an improvement that would be regarding speed. There are 5 main tabs, each selecting a different [main] thing - either grouping and selecting accounts, metrics, sources, platforms, countries and something_id, then the where clauses are also constructed from user input and may be different. This is a custom analytics dashboard if that helps understanding what we are using this for.
A lot of different selects can be chosen by the user and a custom query is constructed based on the user input. I've reduced the select size by excluding the countries because on default it loaded all > 250 countries as where clauses making the the query length pretty ridiculous and embarrassing. For clarification - countries are all marked as selected on default. Pasting it into the answer made me realise that it could be wastly improved. Removing countries if all are selected reduced the load time with parsing from 21 secs~ to 8-10secs (30day foreach loop with basically the select you see on a 14million database rows). Though if the user would exclude at least one country, the sql would be constructed with 250~ where countries. I'm thinking about making an algorithm where if there are only a few of unselected countries to make a where country <> 'unselected' instead of loading all the selected ones ultimately making the query less in size.
EXPLAIN {query}
output andSHOW CREATE TABLE {tblname}
? Do you think you have index the table(s) right? Have you used JOIN rather than LEFT JOIN whereever possible. For multiple conditions on a single table have you considered a generated column with index? – danblack Sep 22 '18 at 8:00SHOW CREATE TABLE {table}
information for table used. A full query would also help a lot. – danblack Sep 25 '18 at 0:29