SQL优化练习

EXPLAIN 
SELECT 
  SUM(amount) 
FROM
  customer a,
  payment b 
WHERE 1 = 1 
  AND a.`customer_id` = b.`customer_id` 
  AND email = 'JENNIFER.DAVIS@sakilacustomer.org' ;

  SELECT COUNT(*) FROM payment;
  SHOW PROFILES;
  SHOW PROFILE source FOR QUERY 5;

  SET optimizer_trace="ENABLED=ON",END_MARKERS_IN_JSON=ON;
  SET optimizer_trace_max_mem_size=1000000;

SELECT rental_id form rental WHERE 1=1 AND rental_date BETWEEN '2005-05-25 04:00:00' AND '2005-05-25 05:00:00';

SELECT rental_id FROM rental WHERE 1=1 AND rental_date >= '2005-05-25 04:00:00' AND rental_date <= '2005-05-25 05:00:00' AND inventory_id = 4466;

SELECT * FROM information_schema.OPTIMIZER_TRACE;

EXPLAIN SELECT SUM(amount) FROM customer a,payment b WHERE 1=1 AND a.`customer_id`=b.`customer_id` AND email = 'ELIZABETH.BROWN@sakilacustomer.org';

ALTER TABLE rental DROP INDEX rental_date;

ALTER TABLE rental ADD INDEX idx_rental_date (rental_date, inventory_id,customer_id);

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25 17:22:10' AND inventory_id=373 AND customer_id=343;

EXPLAIN SELECT * FROM rental WHERE customer_id >= 373 AND customer_id < 400;

ALTER TABLE payment ADD INDEX idx_payment_date(payment_date,amount,last_update);
EXPLAIN SELECT * FROM payment WHERE payment_date = '2006-02-14 15:16:03' AND last_update = '2006-02-15 22:12:32';
EXPLAIN SELECT * FROM payment WHERE amount = 3.98 AND last_update = '2006-02-15 22:12:32';

EXPLAIN SELECT last_update FROM payment WHERE payment_date='2006-02-14 15:16:03' AND  amount = 3.98;

EXPLAIN SELECT last_update FROM payment WHERE amount = 3.98 AND payment_date='2006-02-14 15:16:03' ;

CREATE INDEX ide_title_desc_part ON film_text(title(10),description(20));
EXPLAIN SELECT title FROM film_text WHERE title LIKE 'AFRICAN%';

EXPLAIN SELECT inventory_id FROM rental WHERE rental_date='2006-02-14 15:16:03' AND customer_id >= 300 AND customer_id <= 400;

EXPLAIN SELECT * FROM payment WHERE rental_id IS NULL;

SELECT VERSION();
EXPLAIN SELECT * FROM rental WHERE rental_date='2006-02-14 15:16:03' AND customer_id >=300 AND customer_id <=400;

SHOW STATUS LIKE 'handler_read%';

ANALYZE TABLE payment;

EXPLAIN SELECT * FROM actor WHERE last_name LIKE '%NI%';
EXPLAIN SELECT * FROM (SELECT actor_id FROM actor  WHERE last_name LIKE '%NI%') a, actor b WHERE a.actor_id = b.`actor_id`;

SELECT * FROM actor WHERE last_name LIKE '%NI%';
SELECT * FROM (SELECT actor_id FROM actor  WHERE last_name LIKE '%NI%') a, actor b WHERE a.actor_id = b.`actor_id`;

EXPLAIN SELECT * FROM actor WHERE last_name = 1;
EXPLAIN SELECT * FROM actor WHERE last_name = '1';

EXPLAIN SELECT * FROM payment WHERE amount = 3.98 AND last_update = '2006-02-15 22:12:32';

EXPLAIN 
SELECT * FROM film_text WHERE title LIKE 'SW%';

SELECT * FROM information_schema.OPTIMIZER_TRACE

EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;

ANALYZE TABLE payment;3
ANALYZE LOCAL TABLE payment;
ANALYZE NO_WRITE_TO_BINLOG TABLE payment;

CHECK TABLE payment;
OPTIMIZE TABLE payment;

SHOW CREATE TABLE payment; 

ALTER TABLE payment ENGINE='InnoDB'; 

CREATE VIEW v_payment AS SELECT * FROM payment;
SHOW TABLE FROM payment;

SHOW INDEX FROM payment;
OPTIMIZE TABLE payment;  
ALTER TABLE payment ENGINE='InnoDB'; 

SHOW INDEX FROM customer;

EXPLAIN SELECT * FROM customer ORDER BY store_id;
ALTER TABLE customer ADD INDEX idx_storeid_email(store_id,email);

EXPLAIN SELECT store_id,email,customer_id FROM customer ORDER BY email;

EXPLAIN SELECT store_id,email,customer_id FROM customer WHERE store_id=1 ORDER BY email DESC;

EXPLAIN SELECT store_id,email,customer_id FROM customer WHERE store_id >=1 AND store_id <=3 ORDER BY email DESC;

EXPLAIN SELECT payment_date, SUM(amount) FROM payment GROUP BY payment_date;
EXPLAIN SELECT payment_date, SUM(amount) FROM payment GROUP BY payment_date ORDER BY NULL;

EXPLAIN SELECT * FROM customer WHERE customer_id NOT IN (SELECT customer_id FROM payment);
EXPLAIN SELECT * FROM customer a LEFT JOIN payment b ON a.`customer_id`=b.`customer_id` WHERE b.`customer_id` IS NULL;

EXPLAIN SELECT * FROM customer WHERE store_id = 2 OR email = 'MARY.SMITH@sakilacustomer.org';

SHOW INDEX FROM payment;

EXPLAIN SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
EXPLAIN SELECT a.`film_id`,a.`description` FROM film a INNER JOIN(SELECT film_id FROM film ORDER BY title LIMIT 50,5) b ON a.`film_id`= b.film_id;

EXPLAIN SELECT * FROM payment ORDER BY rental_id DESC LIMIT 410,10;

SELECT payment_id,rental_id FROM payment ORDER BY rental_id DESC LIMIT 400,10;
EXPLAIN SELECT * FROM payment WHERE rental_id < 15640 ORDER BY rental_id DESC LIMIT 10;

SELECT * FROM payment LIMIT 10,-1;

SELECT * FROM payment PROCEDURE ANALYSE();

DESC payment;

EXPLAIN SELECT COUNT(*) FROM rental IGNORE INDEX (idx_rental_date,idx_fk_customer_id);

EXPLAIN SELECT COUNT(rental_id) FROM rental USE INDEX (PRIMARY);

SHOW INDEX idx_rental_date;

EXPLAIN SELECT * FROM rental USE INDEX(idx_fk_inventory_id) WHERE inventory_id > 1;

EXPLAIN SELECT * FROM rental FORCE INDEX(idx_fk_inventory_id) WHERE inventory_id > 1;

SELECT 'abcdefg' REGEXP '.f','abcdefg' REGEXP '.h';

SELECT 'abcdefg' REGEXP '[fhk]';

SELECT * FROM customer ORDER BY RAND() LIMIT 5;

SELECT DATE_FORMAT(payment_date,'%Y-%m'),staff_id,SUM(amount) FROM payment GROUP BY DATE_FORMAT(payment_date,'%Y-%m'),staff_id;

SELECT DATE_FORMAT(payment_date,'%Y-%m'),staff_id,SUM(amount) FROM payment GROUP BY DATE_FORMAT(payment_date,'%Y-%m'),staff_id WITH ROLLUP;

SHOW CREATE TABLE payment;

SELECT * FROM payment PROCEDURE ANALYSE();
SELECT * FROM film_text PROCEDURE ANALYSE(16,256);

SHOW ENGINE INNODB STATUS;