way to perform a SELECT COUNT faster!

Navigation
2

I need to make an inquiry to know a total number of operators I own. But the table has almost 18 million records, and it takes a long time to return almost 3 minutes, and that's because I'm doing direct test in the bank.

The problem will be greater when this table is complete, more than 150 million, then yes it will take forever, does it have any way to perform this query more quickly? 

SELECT *, COUNT(operadora) AS total_op
FROM telefones
GROUP BY operadora

Table Structure 

CREATE TABLE IF NOT EXISTS 'telefones' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'numero' varchar(30) NOT NULL,
  'ddd' int(3) NOT NULL,
  'operadora' varchar(15) NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19606524 ;

--
-- Extraindo dados da tabela 'telefones'
--

INSERT INTO 'telefones' ('id', 'numero', 'ddd', 'operadora') VALUES
(31, '11920160030', 11, 'OI - Celular'),
(32, '11920160031', 11, 'TIM - Celular'),
(33, '11920160032', 11, 'Claro - Celular'),
(34, '11920160033', 11, 'VIVO - Celular'),
(35, '11920160034', 11, 'Claro - Celular'),
(36, '11920160035', 11, 'Claro - Celular'),
(37, '11920160036', 11, 'Claro - Celular'),
(38, '11920160037', 11, 'Claro - Celular'),
(39, '11920160038', 11, 'Claro - Celular'),
(40, '11920160039', 11, 'Claro - Celular'),
(41, '11920160040', 11, 'Claro - Celular'),
(42, '11920160041', 11, 'Claro - Celular'),
(43, '11920160042', 11, 'Claro - Celular'),
(44, '11920160043', 11, 'Claro - Celular'),
(45, '11920160044', 11, 'Claro - Celular'),
(46, '11920160045', 11, 'Claro - Celular'),
(47, '11920160046', 11, 'Claro - Celular'),
(48, '11920160047', 11, 'Claro - Celular'),
(49, '11920160048', 11, 'Claro - Celular'),
(50, '11920160049', 11, 'Claro - Celular'),
(51, '11920160050', 11, 'Claro - Celular'),
(52, '11920160051', 11, 'Claro - Celular'),
(53, '11920160052', 11, 'Claro - Celular'),
(54, '11920160053', 11, 'Claro - Celular'),
(55, '11920160054', 11, 'Claro - Celular'),
(56, '11920160055', 11, 'Claro - Celular'),
(57, '11920160056', 11, 'Claro - Celular'),
(58, '11920160057', 11, 'Claro - Celular'),
(59, '11920160058', 11, 'Claro - Celular'),
(60, '11920160059', 11, 'Claro - Celular'),
(61, '11920160060', 11, 'Claro - Celular'),
(62, '11920160061', 11, 'Claro - Celular'),
(63, '11920160062', 11, 'Claro - Celular'),
(64, '11920160063', 11, 'Claro - Celular'),
(65, '11920160064', 11, 'Claro - Celular'),
(66, '11920160065', 11, 'Claro - Celular'),
(67, '11920160066', 11, 'Claro - Celular'),
(68, '11920160067', 11, 'Claro - Celular'),
(69, '11920160068', 11, 'Claro - Celular'),
(70, '11920160069', 11, 'Claro - Celular'),
(71, '11920160070', 11, 'Claro - Celular'),
(72, '11920160071', 11, 'Claro - Celular'),
(73, '11920160072', 11, 'Claro - Celular'),
(74, '11920160073', 11, 'Claro - Celular'),
(75, '11920160074', 11, 'Claro - Celular'),
(76, '11920160075', 11, 'Claro - Celular'),
(77, '11920160076', 11, 'Claro - Celular'),
(78, '11920160077', 11, 'Claro - Celular'),
(79, '11920160078', 11, 'Claro - Celular'),
(80, '11920160079', 11, 'Claro - Celular'),
(81, '11920160080', 11, 'Claro - Celular'),
(82, '11920160081', 11, 'Claro - Celular'),
(83, '11920160082', 11, 'Claro - Celular'),
(84, '11920160083', 11, 'Claro - Celular'),
(85, '11920160084', 11, 'Claro - Celular'),
(86, '11920160085', 11, 'Claro - Celular'),
(87, '11920160086', 11, 'Claro - Celular'),
(88, '11920160087', 11, 'Claro - Celular'),
(89, '11920160088', 11, 'Claro - Celular'),
(90, '11920160089', 11, 'Claro - Celular');
    
asked by anonymous 09.03.2017 / 20:04

1 answer

1

There is not much you can do, but you can try to figure out the reason for the problem with EXPLAIN .

First, MySQL tries to always use indexes , your table does not have any indexing of this column, why not create?

MySQL has three choices:

  • Uses index .

  • If you can not use index (not exist is one of the reasons) :

    • You will use a "temporary table" .
    • Use "filesort" .

You can force the temporary table using the SELECT SQL_SMALL_RESULT ... or force the filesort using the SELECT SQL_BIG_RESULT ... as it appears in the manual of SELECT .

You can create index using CREATE INDEX ... , as in the manual or using ALTER TABLE , for example: 

CREATE INDEX nome_do_index on nome_da_tabela(nome_da_coluna) using HASH;

The reason for using HASH instead of BTree is because you will not compare "operatorA> operatorB", but will compare "operatorA! = operatorB" or "operatorA = operatorA".

One way to get around the problem is to create a table "Count_Operator" and update it frequently, ie a "CACHE". So, instead of always calculating, you just have to SELECT contagem WHERE operadora = 'oi' , as it is already pre-calculated the return will be much faster.

    
09.03.2017 / 20:51
Uploading images in asp net core 1.0 Help - site one page scroll