Categories
Database Development

how to optimize big result to sum in MySQL 5.7

Now I have a user consume history record table, sometimes the data is very big(>1million) per day.Now I have to calculate the table to get report data,this is my sql: SELECT SUM(consume_num) AS consumeNum, SUM(transin_num) AS transinNum , CASE WHEN COUNT(DISTINCT transin_user_num) – 1 < 0 THEN 0 ELSE COUNT(DISTINCT transin_user_num) – 1 END AS […]

Categories
Database Development

Why do these queries deadlock and how to work this around in MariaDB?

The following 2 queries enounter DB deadlock in my program: DELETE FROM proxies WHERE NOT EXISTS ( SELECT 1 FROM proxy_xref_proxy_list AS pxpl WHERE pxpl.ProxyID=proxies.ID AND (pxpl.DeactivationTS IS NULL OR pxpl.DeactivationTS >= (NOW() – INTERVAL 24 HOUR)) ORDER BY pxpl.ProxyListID LIMIT 1 ) ORDER BY proxies.ID INSERT IGNORE INTO proxy_xref_proxy_list (ProxyID, ProxyListID) (SELECT ID, 13 […]

Categories
Database Development

Bulk INSERT … ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB

I have a table like the following: CREATE TABLE `demo_upsert` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `Url` TEXT NOT NULL COLLATE ‘utf8mb4_unicode_ci’, `LastListID` BIGINT(20) NULL DEFAULT NULL, `Statistics` BIGINT(20) NOT NULL DEFAULT ‘0’, PRIMARY KEY (`ID`) USING BTREE, UNIQUE INDEX `Url` (`Url`) USING HASH ) COLLATE=’utf8mb4_unicode_ci’ ENGINE=InnoDB ; When I try a bulk upsert operation: […]