Optimize SQL query on a large table

Question

First of all, this question concerns MySQL 3.23.58, so keep in mind.

I have 2 tables with the following definition:

Table A: id INT (primary), customer_id INT, offlineid INT

Table B: id INT (primary), name VARCHAR(255)

Now table A contains a range of 65k + records, and table B contains ~ 40 records. In addition to the two primary key indexes in table A, there is an index in the offlineid field. Each table has more fields, but they are not relevant (as I see, ask if necessary) for this query.

The first request was submitted to me for the first time (request time: ~ 22 seconds):

SELECT b.name, COUNT(*) AS orders, COUNT(DISTINCT(a.kundeid)) AS leads
FROM katalogbestilling_katalog a, medie b
WHERE a.offlineid = b.id
GROUP BY b.name

Now each id in medie is associated with a different name, which means that you can group by both id and name. A little testing back and forth helped me with this (request time: ~ 6 seconds):

SELECT a.name, COUNT(*) AS orders, COUNT(DISTINCT(b.kundeid)) AS leads
FROM medie a
INNER JOIN katalogbestilling_katalog b ON a.id = b.offline
GROUP BY b.offline;

"" ( 1 )? offlineid, , , , . EXPLAIN , fileshort ( ). !

Answer 1

, . - , , , . , , , . Oracle MS SqlServer.

, :

medie.id, medie.name
katalogbestilling_katalog.offlineid, katalogbestilling_katalog.kundeid

. , .

:

Answer 2

, , ​​ MySQL. , MySQL 3 COUNT (DISTINCT()).

. ?

, MySQL 3 . , , , , .

Answer 3

, mysql 3 . , .

Answer 4

kundeid? ( MySQL, ), EXPLAIN .

, , - , , , ( RESET QUERY CACHE ). - , . :

SELECT b.name, COUNT(*) AS orders, COUNT(DISTINCT(a.kundeid)) AS leads
FROM katalogbestilling_katalog a, medie b
WHERE a.offlineid = b.id
GROUP BY b.name

SELECT b.name, COUNT(DISTINCT(a.kundeid)) AS leads
FROM katalogbestilling_katalog a, medie b
WHERE a.offlineid = b.id
GROUP BY b.name

"", ,

SELECT b.name, COUNT(*) AS orders
FROM katalogbestilling_katalog a, medie b
WHERE a.offlineid = b.id
GROUP BY b.name

"" . .

update. - . , , . ,

SELECT b.name
FROM katalogbestilling_katalog a, medie b
WHERE a.offlineid = b.id
GROUP BY b.name

? , , ( , - JOIN , , , - , , GROUP BY).

EXPLAIN SELECT b.name
FROM katalogbestilling_katalog a, medie b
WHERE a.offlineid = b.id
GROUP BY b.name

( )?

# 2

, , mysql, - , .

Answer 5

, select, (*) PK.

SELECT a.name, COUNT(*) AS orders, COUNT(DISTINCT(b.kundeid)) AS leads FROM medie aINNER JOIN katalogbestilling_katalog b ON a.id = b.offline GROUP BY b.offline;

SELECT a.name, COUNT(a.id) AS orders, (SELECT COUNT(kundeid) FROM katalogbestilling_katalog b WHERE b.offline = a.id) AS Leads FROM medie a;

Answer 6

, , , A, , . , .

, MSSQL, .

Answer 7

, 65k + 40k :)

katalogbestilling_katalog.offline, .

Answer 8

(offlineid, kundeid)

180 000 BS 30 000 BS medie ( katalog offlineid, medie kundeid, , ). , mysql 5, , , mysql 3 , , , mysql 3 .

:

CREATE TABLE `katalogbestilling_katalog` (
  `id` int(11) NOT NULL auto_increment,
  `offlineid` int(11) NOT NULL,
  `kundeid` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `offline_id` (`offlineid`,`kundeid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=60001 ;

CREATE TABLE `medie` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=30001 ;

:

SELECT b.name, COUNT(*) AS orders, COUNT(DISTINCT(a.kundeid)) AS leads
FROM medie b
INNER JOIN katalogbestilling_katalog a ON b.id = a.offlineid
GROUP BY a.offlineid
LIMIT 0 , 30


"Showing rows 0 - 29 (30,000 total, Query took 0.0018 sec)"

:

id:  1
select_type:    SIMPLE
table: a
type: index
possible_keys:  NULL
key:    offline_id
key_len:    8
ref: NULL
rows: 180000
Extra: Using index

id: 1
select_type:    SIMPLE
table: b
type: eq_ref
possible_keys:  PRIMARY
key:    PRIMARY
key_len:    4
ref: test.a.offlineid
rows: 1
Extra:

Answer 9

. . . InnoDB, MyISAM. , , ( (*) MyISAM, InnoDB, ). , MySQL Forge

Answer 10

:

SELECT fieldlist FROM A
SELECT fieldlist FROM B

mysql , , db. 65k .