1

I have a database with 11 tables and a total count of ~120k rows. I do the following query, to get the data of all tables:

SELECT SQL_CALC_FOUND_ROWS 
           char.id, char.firstname, char.lastname, char.commonname, 
           char.picture, nation.id AS nationId, nation.name AS nationName,
           nation.logo AS nationLogo, club.id AS clubId, club.name AS clubName, 
           club.logo AS clubLogo, league.id AS leagueId, league.name AS leagueName, 
           league.logo AS leagueLogo, card.name, char.rating, char.position, 
           pac.pace, sho.shooting, pas.passing, dri.dribbling, def.defending, 
           phy.physical, char.skill, char.weakfoot, char.quality, char.url
FROM       `characteristics` AS `char`
INNER JOIN `nation` AS `nation` 
ON         `nation`.`id` = `char`.`nation`
INNER JOIN `club` AS `club` 
ON         `club`.`id` = `char`.`club`
INNER JOIN `league` AS `league` 
ON         `league`.`id` = `char`.`league`
INNER JOIN `card` AS `card` 
ON         `card`.`id` = `char`.`color`
INNER JOIN `pace` AS `pac` 
ON         `pac`.`id` = `char`.`id`
INNER JOIN `shooting` AS `sho` 
ON         `sho`.`id` = `char`.`id`
INNER JOIN `passing` AS `pas` 
ON         `pas`.`id` = `char`.`id`
INNER JOIN `dribbling` AS `dri` 
ON         `dri`.`id` = `char`.`id`
INNER JOIN `defending` AS `def` 
ON         `def`.`id` = `char`.`id`
INNER JOIN `physical` AS `phy` 
ON         `phy`.`id` = `char`.`id`          
WHERE FIELD(`char`.`Position`, "CB")

This query takes about 1.5 seconds (don't know if this is normal speed or fast/slow). Can I optimize this query to get it faster? If so maybe some of you could tell me the way to do this?!

Here is the explain query:

enter image description here

Hope there is someone who can help me ;)

Edit: Show Create for the Joins: Club

    CREATE TABLE `club` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `name` varchar(150) NOT NULL,
 `logo` varchar(200) NOT NULL,
 `league` int(3) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `league` (`league`)
) ENGINE=InnoDB AUTO_INCREMENT=113148 DEFAULT CHARSET=utf8

League

CREATE TABLE `league` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `abbrname` varchar(10) NOT NULL,
 `logo` varchar(200) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2119 DEFAULT CHARSET=utf8

Nation

CREATE TABLE `nation` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `name` varchar(150) NOT NULL,
 `logo` varchar(200) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=utf8

Card

CREATE TABLE `card` (`id` int(2) NOT NULL AUTO_INCREMENT,
 `name` enum('TotY','TotS','Legend','RB','Hero','iMotM','MotM','TotW','Green','Gold','Gold Nonrare','Silver','Silver Nonrare','Bronze','Bronze Nonrare') NOT NULL,
 `logo` varchar(200) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

Pace

CREATE TABLE `pace` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `pace` int(2) NOT NULL,
 `acceleration` int(2) NOT NULL,
 `sprint_speed` int(2) NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `pace` FOREIGN KEY (`id`) REFERENCES `player` (`pace`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17041 DEFAULT CHARSET=utf8

Defending

CREATE TABLE `defending` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `defending` int(2) NOT NULL,
 `interceptions` int(2) NOT NULL,
 `heading` int(2) NOT NULL,
 `marking` int(2) NOT NULL,
 `standing_tackle` int(2) NOT NULL,
 `sliding_tackle` int(2) NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `defending` FOREIGN KEY (`id`) REFERENCES `player` (`defending`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17041 DEFAULT CHARSET=utf8

Dribbling

CREATE TABLE `dribbling` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `dribbling` int(2) NOT NULL,
 `agility` int(2) NOT NULL,
 `balance` int(2) NOT NULL,
 `reactions` int(2) NOT NULL,
 `ball_control` int(2) NOT NULL,
 `dribblings` int(2) NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `dribbling` FOREIGN KEY (`id`) REFERENCES `player` (`dribbling`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17041 DEFAULT CHARSET=utf8

Shooting

CREATE TABLE `shooting` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `shooting` int(2) NOT NULL,
 `positioning` int(2) NOT NULL,
 `finishing` int(2) NOT NULL,
 `shot_power` int(2) NOT NULL,
 `long_shots` int(2) NOT NULL,
 `volleys` int(2) NOT NULL,
 `penalties` int(2) NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `shooting` FOREIGN KEY (`id`) REFERENCES `player` (`shooting`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17041 DEFAULT CHARSET=utf8

Physical

CREATE TABLE `physical` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `physical` int(2) NOT NULL,
 `jumping` int(2) NOT NULL,
 `stamina` int(2) NOT NULL,
 `strength` int(2) NOT NULL,
 `aggression` int(2) NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `physical` FOREIGN KEY (`id`) REFERENCES `player` (`physical`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17041 DEFAULT CHARSET=utf8

Passing

CREATE TABLE `passing` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `passing` int(2) NOT NULL,
 `vision` int(2) NOT NULL,
 `crossing` int(2) NOT NULL,
 `free_kick` int(2) NOT NULL,
 `short_passing` int(2) NOT NULL,
 `long_passing` int(2) NOT NULL,
 `curve` int(2) NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `passing` FOREIGN KEY (`id`) REFERENCES `player` (`passing`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17041 DEFAULT CHARSET=utf8

Characteristics

CREATE TABLE `characteristics` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `url` varchar(100) NOT NULL,
 `firstname` varchar(75) NOT NULL,
 `lastname` varchar(75) NOT NULL,
 `commonname` varchar(75) DEFAULT NULL,
 `age` int(2) NOT NULL,
 `height` varchar(35) NOT NULL,
 `nation` int(5) NOT NULL,
 `club` int(5) NOT NULL,
 `league` int(5) NOT NULL,
 `picture` varchar(200) NOT NULL,
 `position` enum('GK','RWB','RB','CB','LB','LWB','CDM','RM','CM','LM','CAM','RW','CF','LW','RF','ST','LF') NOT NULL,
 `rating` int(2) NOT NULL,
 `quality` enum('Gold','Silver','Bronze') NOT NULL,
 `color` int(2) NOT NULL,
 `skill` int(1) NOT NULL,
 `weakfoot` int(1) NOT NULL,
 `strongfoot` enum('Right','Left') NOT NULL,
 `workrates` enum('Low / Low','Low / Medium','Low / High','Medium / Low','Medium / Medium','Medium / High','High / Low','High / Medium','High / High') NOT NULL,
 `traits` varchar(500) DEFAULT NULL,
 `specialities` varchar(500) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17041 DEFAULT CHARSET=utf8

Edit 2: EXPLAIN when using ´WHERE Position = 'CB' OR Position = 'ST'´. If I write the query without the OR-Part it will be fine, but with multiple parts there is the same slow outcome like before: enter image description here

  • 2
    What are you going to do with all 120k rows once they have been selected? – Philᵀᴹ Mar 31 '17 at 10:23
  • I'm using datatables.net serverside, don't need all of these 120k rows, but a faster query, cause the ajax transfer needs 5 secs, which is too long I guess... – Hans Martin Mar 31 '17 at 11:58
  • 1
    Can you post results of show create table <table name> for all tables in the join? – thatsaru Mar 31 '17 at 13:06
  • Did in the edit ;) – Hans Martin Apr 1 '17 at 11:05
  • You say you don't need all the 120K rows. So how many rows does the query return? And can you show a sample data of the char.Position column? – ypercubeᵀᴹ Apr 1 '17 at 11:32
1

First of all, consider rewriting your WHEREcondition. If instead of using a FIELD function you use a direct equal comparison, and have an index (KEY) on column Position, you allow MySQL to use it. As of now, I guess it needs to do a full table scan to retrieve the rows. The other JOIN conditions will need to be evaluated in all cases...

You can check a simplified scenario:

 CREATE TABLE characteristics
 (
     id integer primary key,
     firstname character varying(100),
     lastname  character varying(100),
     picture character varying(100),
     position character varying(100),
     -- many other columns
     key(position)
 ) ;

 INSERT INTO characteristics 
 VALUES (1, 'John', 'Doe', 'pic1', 'CB') ;

 INSERT INTO characteristics 
 VALUES (2, 'Joe', 'Smith', 'pic2', 'A1') ;

We make a select using FIELD function:

 SELECT  
      c.id, c.firstname, c.lastname
 FROM 
     characteristics AS c
 WHERE 
     FIELD(c.position, 'CB') ;

 id | firstname | lastname
 -: | :-------- | :-------
  1 | John      | Doe

This is the execution plan:

 EXPLAIN
 SELECT  
      c.id, c.firstname, c.lastname
 FROM 
     characteristics AS c
 WHERE 
    FIELD(c.position, 'CB') ;

 id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra
-: | :---------- | :---- | :--- | :------------ | :--- | :------ | :--- | ---: | :---------- 1 | SIMPLE | c | ALL | null | null | null | null | 2 | Using where

No keys can be used (i.e.: sequential scan of all characteristics table)

We check a second time by using an equals comparison:

 SELECT  
      c.id, c.firstname, c.lastname
 FROM 
     characteristics AS c
 WHERE 
     c.position = 'CB' ;

We get the same result:

 id | firstname | lastname
 -: | :-------- | :-------
  1 | John      | Doe

But the execution plan is different:

 EXPLAIN
 SELECT  
      c.id, c.firstname, c.lastname
 FROM 
     characteristics AS c
 WHERE 
     c.position = 'CB' ;

 id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra
-: | :---------- | :---- | :--- | :------------ | :------- | :------ | :---- | ---: | :-------------------- 1 | SIMPLE | c | ref | position | position | 103 | const | 1 | Using index condition

You can check everything at:

dbfiddle here

After you've made this change, consider whether you actually need to further optimize your query. [For instance: do you actually need the information of all 11 tables in all cases?]

  • Thanks for the response, like a wrote in my comment, I was focused on a multiple selection e.g. Position=CB and =something else, so I choose field, but I can do it with multiple wheres. But it's nice to have an visual overview to get the different ;) – Hans Martin Apr 1 '17 at 14:02
  • 1
    WHERE position = 'CB' OR position = 'AD' OR ... or WHERE position IN ('CB', 'AD', ...) to be more concise. – ypercubeᵀᴹ Apr 1 '17 at 14:12
  • 1
    AFAIK: Using a function FIELD(x, y) prevents MySQL from using an index. Latest versions would use it in both the IN and =x OR =y situations. Obviously, optimizing a query depends on several factors, an important one of which is the specific WHERE clause. – joanolo Apr 1 '17 at 14:15
  • So I tried to use ´WHERE position = 'CB' OR position = 'ST'´ and get the edited EXPLAIN above in the question. If I do a where with more than one position ´type = all´, ´key=null´ and ´ref=null´ too. Shouldn't it be a specific key and not null? – Hans Martin Apr 6 '17 at 12:58
0

(To long for a comment.)

Do not make multiple tables, each with AUTO_INCREMENT and then JOIN on those ids. If they ever get out of sync, your data set will be irrecoverably hosed.

In your case, probably characteristics should have the AUTO_INCREMENT.

It is usually poor practice to have multiple tables in a 1:1 relation. You seem to have 7 such tables. Either combine them into a single table, or somehow justify the split. That will cut down on the number of JOINs, which will speed up the query.

INT(2) and INT(5) are identical. Each allows values up to 2 billion. An INT always takes 4 bytes. Probably want TINYINT UNSIGNED (1 byte, values 0..255) and SMALLINT UNSIGNED (2 bytes, 0..65K). Saving space will help the speed some.

Once you have changed WHERE FIELD(char.Position, "CB") to WHERE char.Position IN ('CB') (as others have mentioned), the explain plan will start with char instead of pas.

It does not make sense to use SQL_CALC_FOUND_ROWS in a query that does not have a LIMIT clause. Simply count the number of rows in the resultset.

Is 1.5 seconds fast or slow for 15K rows returned? Quote possibly that is the time it takes to shovel several megabytes out of the MySQL server, across the network, and into the client. What will you do with the results? If you display it on a web page, it will be unwieldy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.