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;
SQL优化练习
最新推荐文章于 2023-06-27 17:34:33 发布