Search code examples
mysqldatabaseperformancequery-optimization

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

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);
Solution

  • 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.