How to optimise simple MySql query of a 50M row table

Question

I have a MySql table with a little over 50M records (over 13Gb); there are 24 numerical fields (int), one varchar and the PK. All the fields are indexed, and yet simple queries like SELECT COUNT(gID) FROM tgame t WHERE t.gPri=1 OR t.gPri=2 take over three minutes to run. The data are static, so there will be no more records added or deleted. What could be done to improve performance? The current state of affairs renders the application unviable.

Edit: MySql version 8.0.29. The physical machine has 32Gb, if that's what tadman means by server. I don't have any composite indices and as it may be obvious by now, don't know the first thing about performance metrics, optimisation or tuning.

SELECT COUNT(*) FROM tgame t WHERE t.gPri IN (1,2) did help, it ran in 5.63 seconds, which is comparatively much better, acceptable even. However, adding a clause such as SELECT COUNT(*) FROM tgame t WHERE t.gPri IN (1,2) AND t.gPar=2 takes a minute and a half.

Typically I expect to have queries that combine several of those fields.

Here's the EXPLAIN: enter image description here

-- 
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- Set default database
--
USE msenadb;

--
-- Create table `tgame`
--
CREATE TABLE tgame (
  gID int NOT NULL AUTO_INCREMENT,
  gN int NOT NULL,
  gGame varchar(20) NOT NULL,
  gInts int NOT NULL,
  gSumTot int NOT NULL,
  gQdts int NOT NULL,
  gSeq int NOT NULL,
  gPar int NOT NULL,
  gTri int NOT NULL,
  gPri int NOT NULL,
  gSqr int NOT NULL,
  gFib int NOT NULL,
  gGold int NOT NULL,
  gTsl int NOT NULL,
  gTslAc int NOT NULL,
  gSumAlg int NOT NULL,
  gWinner bit(1) DEFAULT b'0',
  gLns int DEFAULT 0,
  gCols int DEFAULT 0,
  gd1 int DEFAULT 0,
  gd2 int DEFAULT 0,
  gd3 int DEFAULT 0,
  gd4 int DEFAULT 0,
  gd5 int DEFAULT 0,
  gd6 int DEFAULT 0,
  g3 int DEFAULT 0,
  PRIMARY KEY (gID)
)
ENGINE = MYISAM,
AUTO_INCREMENT = 50065972,
AVG_ROW_LENGTH = 80,
CHARACTER SET utf8mb4,
CHECKSUM = 0,
COLLATE utf8mb4_0900_ai_ci,
MAX_ROWS = 51000000;

--
-- Create index `ign` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX ign (gN);

--
-- Create index `igints` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igints (gInts);

--
-- Create index `igsumtot` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igsumtot (gSumTot);

--
-- Create index `igqdts` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igqdts (gQdts);

--
-- Create index `igseq` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igseq (gSeq);

--
-- Create index `igpar` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igpar (gPar);

--
-- Create index `igtri` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igtri (gTri);

--
-- Create index `igpri` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igpri (gPri);

--
-- Create index `igsqr` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igsqr (gSqr);

--
-- Create index `igfib` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igfib (gFib);

--
-- Create index `iggold` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX iggold (gGold);

--
-- Create index `igtsl` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igtsl (gTsl);

--
-- Create index `igtslac` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igtslac (gTslAc);

--
-- Create index `igsumalg` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igsumalg (gSumAlg);

--
-- Create index `igwinner` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igwinner (gWinner);

--
-- Create index `igd1` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igd1 (gd1);

--
-- Create index `igd2` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igd2 (gd2);

--
-- Create index `igd3` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igd3 (gd3);

--
-- Create index `igd4` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igd4 (gd4);

--
-- Create index `igd5` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igd5 (gd5);

--
-- Create index `igd6` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igd6 (gd6);

--
-- Create index `igg3` on table `tgame`
--
ALTER TABLE tgame
ADD INDEX igg3 (g3);

--
-- Create index `iggame` on table `tgame`
--
ALTER TABLE tgame
ADD FULLTEXT INDEX iggame (gGame);

@tadman ```id: 1; select_type: SIMPLE; table: t; partitions: (null); type: ALL; possible_keys: igpri; key: (null); key_len: (null); ref: (null); rows: 50063860; filtered: 66,05; Extra: Using where``` — developer1405, Aug 02 '22 at 20:53
How much memory does InnoDB have to work with on your server? Have you tuned it properly? Have you observed the server performance metrics with a tool like MySQL Workbench? — tadman, Aug 02 '22 at 21:03
Does `t.gPri IN (1,2)` help any? Is `gPri` indexed by itself, or is it part of a composite index where it's not the first entry? — tadman, Aug 02 '22 at 21:04
@developer1405 Additional information request, please. # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. — Wilson Hauck, Aug 13 '22 at 18:24

score 0 · Answer 1 · answered Aug 02 '22 at 20:47

0

take a look at this answer, MySQL optimization of huge table it has a collection of answers to problems similar to yours

answered Aug 02 '22 at 20:47

Tareq Alsayed

64
4

score 0 · Answer 2 · answered Aug 02 '22 at 22:37

Depending on how varied the queries against this data are likely to be, there could be an alternative to tuning the server’s performance. You mention that the data is static so you could have an additional table to store the counts of different columns and values and then just query that rather than counting up the same rows all the time just to get the same answers.

score 0 · Answer 3 · answered Aug 02 '22 at 22:40

SELECT COUNT(gID)         -- This checks whether gID is NULL
    FROM tgame t
    WHERE t.gPri=1 OR t.gPri=2;   -- OR is often deadly for performance

-->

SELECT COUNT(*)        -- This counts rows
    FROM tgame t
    WHERE t.gPri IN (1, 2);

Please provide SHOW CREATE TABLE and EXPLAIN SELECT ...

score 0 · Accepted Answer · answered Aug 13 '22 at 18:19

From your question, "However, adding a clause such as SELECT COUNT(*) FROM tgame t WHERE t.gPri IN (1,2) AND t.gPar=2 takes a minute and a half."

May be corrected with ALTER TABLE tgame ADD INDEX tgame_CI_gPri_gPar (gPri,gPar); because you NEED this composite index to minimize media access in your 50M row table.

How to optimise simple MySql query of a 50M row table

4 Answers4