Tempo gasto pelas consultas

pessoal,

trago para vocês, alguns teste que fiz em uma tabela que tenho, com 1.802.011 registro, criados aleatoriamente.

é agora que agente vê o porque das dicas que o pessoal do forum nos dá, e quando fazemos os testes em tabelas pequenas, parecem que nem faze diferença, mas em trabelas maiores como essa, a diferença aparece.

bem, vamos lá

****

USANDO ENGINE MYISAN - TAMANHO DA TABELA 95MB

CREATE TABLE orders_range (

id INT(11) NOT NULL AUTO_INCREMENT,
customer_surname VARCHAR(100) NULL DEFAULT NULL,
store_id INT(11) NULL DEFAULT NULL,
PRIMARY KEY (id),
INDEX store_id (store_id)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DYNAMIC

SELECT * FROM `orders_range` WHERE `customer_surname` = 'Dewey Cardinale';

/ 0 rows affected, 150 rows found. Duration for 1 query: 0,031 sec. /

SELECT * FROM `orders_range` WHERE `customer_surname` = 'Tyler Burkart';

/ 0 rows affected, 150 rows found. Duration for 1 query: 0,016 sec. /

SELECT * FROM orders_range WHERE customer_surname LIKE 'LUIGI%';
/ 0 rows affected, 1.202 rows found. Duration for 1 query: 0,125 sec. /

SELECT * FROM orders_range WHERE customer_surname LIKE 'Tyler%';
/ 0 rows affected, 1.502 rows found. Duration for 1 query: 0,094 sec. /

SELECT * FROM orders_range WHERE customer_surname LIKE '%LUIGI%';
/ 0 rows affected, 1.202 rows found. Duration for 1 query: 1,203 sec. /

SELECT * FROM orders_range WHERE customer_surname LIKE '%UIGI%';
/ 0 rows affected, 1.202 rows found. Duration for 1 query: 1,172 sec. /

SELECT * FROM orders_range WHERE customer_surname IN('Dewey Cardinale', 'Tyler Burkart');
/ 0 rows affected, 300 rows found. Duration for 1 query: 0,016 sec. /

SELECT * FROM `orders_range` WHERE `store_id` = 4113;

/ 0 rows affected, 174 rows found. Duration for 1 query: 0,094 sec. /

SELECT * FROM `orders_range` WHERE `store_id` = 4125;

/ 0 rows affected, 191 rows found. Duration for 1 query: 0,015 sec. /

SELECT * FROM orders_range WHERE store_id LIKE ('91%');
/ 0 rows affected, 20.577 rows found. Duration for 1 query: 1,015 sec. /

SELECT * FROM orders_range WHERE store_id LIKE ('911%');
/ 0 rows affected, 2.062 rows found. Duration for 1 query: 0,906 sec. /

SELECT * FROM orders_range WHERE store_id LIKE ('%11%');
/ 0 rows affected, 51.138 rows found. Duration for 1 query: 1,422 sec. /

SELECT * FROM orders_range WHERE store_id LIKE ('%911%');
/ 0 rows affected, 3.580 rows found. Duration for 1 query: 1,000 sec. /

SELECT * FROM orders_range WHERE store_id IN(9145, 4113);
/ 0 rows affected, 354 rows found. Duration for 1 query: 0,234 sec. /

SELECT * FROM orders_range WHERE store_id IN(1598, 4112);
/ 0 rows affected, 385 rows found. Duration for 1 query: 0,078 sec. /

USANDO ENGINE MYISAN APOS TER SIDO CRIADO O ÍNDICE FULLTEXT - TAMANHO DA TABELA 132MB

CREATE TABLE orders_range (
id INT(11) NOT NULL AUTO_INCREMENT,
customer_surname VARCHAR(100) NULL DEFAULT NULL,
store_id INT(11) NULL DEFAULT NULL,
PRIMARY KEY (id),
INDEX store_id (store_id),
FULLTEXT INDEX customer_surname (customer_surname)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DYNAMIC

SELECT * FROM orders_range WHERE MATCH (customer_surname) AGAINST ('Russell');
/ 0 rows affected, 1.202 rows found. Duration for 1 query: 0,046 sec. /

SELECT * FROM orders_range WHERE MATCH (customer_surname) AGAINST ('Ismael Ornellas');
/ 0 rows affected, 1.652 rows found. Duration for 1 query: 0,203 sec. /

****

USANDO ENGINE INNOBD - TAMANHO DA TABELA 191MB

CREATE TABLE `orders_range_inno` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`customer_surname` VARCHAR(100) NULL DEFAULT NULL,
	`store_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `store_id` (`store_id`),
	INDEX `customer_surname` (`customer_surname`)
)

COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DYNAMIC

SELECT * FROM `orders_range_inno` WHERE `customer_surname` = 'Rhett Leverette';

/ 0 rows affected, 150 rows found. Duration for 1 query: 0,062 sec. /

SELECT * FROM `orders_range_inno` WHERE `customer_surname` = 'Johnson Morency';

/ 0 rows affected, 150 rows found. Duration for 1 query: 0,016 sec. /

SELECT * FROM orders_range_inno WHERE customer_surname LIKE 'Refugio%';
/ 0 rows affected, 1.201 rows found. Duration for 1 query: 4,610 sec. /

SELECT * FROM orders_range_inno WHERE customer_surname LIKE 'Damien%';
/ 0 rows affected, 1.201 rows found. Duration for 1 query: 4,329 sec. /

SELECT * FROM orders_range_inno WHERE customer_surname LIKE '%Cameron%';
/ 0 rows affected, 902 rows found. Duration for 1 query: 3,859 sec. /

SELECT * FROM orders_range_inno WHERE customer_surname LIKE '%rnulfo%';
/ 0 rows affected, 1.804 rows found. Duration for 1 query: 3,047 sec. /

SELECT * FROM orders_range_inno WHERE customer_surname IN('Erich Golightly', 'Buford Degroat');
/ 0 rows affected, 300 rows found. Duration for 1 query: 0,078 sec. /

SELECT * FROM orders_range WHERE customer_surname IN('Johnson Morency', 'Jeffrey Gries');
/ 0 rows affected, 300 rows found. Duration for 1 query: 0,078 sec. /

SELECT * FROM `orders_range_inno` WHERE `store_id` = 4536;

/ 0 rows affected, 174 rows found. Duration for 1 query: 0,016 sec. /

SELECT * FROM `orders_range_inno` WHERE `store_id` = 5623;

/ 0 rows affected, 183 rows found. Duration for 1 query: 0,032 sec. /

SELECT * FROM orders_range_inno WHERE store_id LIKE ('562%');
/ 0 rows affected, 1.949 rows found. Duration for 1 query: 2,704 sec. /

SELECT * FROM orders_range_inno WHERE store_id LIKE ('45%');
/ 0 rows affected, 20.370 rows found. Duration for 1 query: 2,922 sec. /

SELECT * FROM orders_range_inno WHERE store_id LIKE ('%34%');
/ 0 rows affected, 54.914 rows found. Duration for 1 query: 3,360 sec. /

SELECT * FROM orders_range_inno WHERE store_id IN(3456, 2563);
/ 0 rows affected, 408 rows found. Duration for 1 query: 0,156 sec. /

SELECT * FROM orders_range_inno WHERE store_id IN(4236, 2365);
/ 0 rows affected, 364 rows found. Duration for 1 query: 0,031 sec. /

bom, os resultados estão ai. Tirem suas próprias conclusões.

espero que façam bom proveito!

até