让我们从一个简单的例子开始:
CREATE TABLE `test` (
`id` INT UNSIGNED NOT NULL,
`value` CHAR(12) NOT NULL,
INDEX (`id`),
INDEX (`value`)
) ENGINE = InnoDB;
所以两列都被索引了。我认为这意味着MySQL不再需要读取实际的表了,因为所有的数据都存储在索引中。
mysql> EXPLAIN SELECT id FROM test WHERE id = 1;
+----+-------------+-------+------+---------------+------
有没有人能解释一下为什么我会看到以下行为:
mysql> show index from history_historyentry;
+----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name
这是我的MySQL查询:
SELECT [columns] FROM article a
LEFT JOIN category_article ca ON a.id=ca.aid
LEFT JOIN category c1 ON c1.id=ca.cid
LEFT JOIN navigation n ON n.cid=c1.id
LEFT JOIN navigation nn ON nn.id=n.parent
LEFT JOIN category c2 ON nn.cid=c2.id
GROUP BY a
我正在对mysql表使用索引。
我的查询是这样的
EXPLAIN SELECT * FROM `logs` WHERE userId =288 AND dateTime BETWEEN '2010-08-01' AND '2010-08-27'
我对这个表日志的字段userId进行了索引,解释查询的结果如下所示。
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE logs
表结构:
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| total | int(11) |
mysql> desc users;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL
请考虑以下方案
CREATE table articles (
id Int UNSIGNED NOT NULL AUTO_INCREMENT,
cat_id Int UNSIGNED NOT NULL,
status Int UNSIGNED NOT NULL,
date_added Datetime,
Primary Key (id)) ENGINE = InnoDB;
CREATE INDEX cat_list_INX ON articles (cat_id, status, date_added);
CREATE INDEX categori
考虑一个包含以下字段的表:
mysql> DESCRIBE my_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| pk | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | U
我在数据库中有一个表‘post’,它在user_id上有非唯一的索引(键: MUL)。
mysql> show columns from posts;
+---------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+-------------------+--------
有时我会遇到一种奇怪的MySQL行为。让我们假设我有索引(type,rel,created),(type),(rel)。对于这样的查询,最佳选择是:
SELECT id FROM tbl
WHERE rel = 3 AND type = 3
ORDER BY created;
就是使用索引(type, rel, created)。但是MySQL决定将索引(type)和(rel)相交,这会导致较差的性能。下面是一个示例:
mysql> EXPLAIN
-> SELECT id FROM tbl
-> WHERE rel = 3 AND type = 3
非常奇怪的是:
mysql> explain select *from online where last < now()-interval 30 second and type=1;
+----+-------------+--------+------+---------------------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | ke
设置:
mysql> create table t(a integer unsigned,b integer unsigned);
mysql> insert into t(a,b) values (1,2),(1,3),(2,4);
mysql> create index i_t_a on t(a);
mysql> create index i_t_b on t(b);
mysql> explain select * from t where a=1 or b=4;
+----+-------------+-------+------+-------------
因此,我从这个查询开始:
SELECT * FROM TABLE1 WHERE hash IN (SELECT id FROM temptable);
这花了我很长时间,所以我运行了一个解释:
mysql> explain SELECT * FROM TABLE1 WHERE hash IN (SELECT id FROM temptable);
+----+--------------------+-----------------+------+---------------+------+---------+------+------------+-------------+
|
我有以下MySQL查询
explain select item_id from items use index(user_item_id) where user_id=9 and item_id=10000
返回值如下
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE items ref user_item_id user_item_id 8 const,const
这里是MySQL noob。
我正在尝试运行下面的语句到
EXPLAIN SELECT * FROM actor as a
INNER JOIN film_actor as fa on a.actor_id = fa.actor_id
INNER JOIN film AS f ON fa.film_id = f.film_id;
输出是
id| select_type| table | partitions | type | possible_keys | key | key_len | ref | ro
所以我有一个疑问:
UPDATE ref_records SET count = count + 1 WHERE ref_url = 'http://www.example.com/' AND user_id = '32366' LIMIT 1
我在每个"ref_url“和"user_id”上分别设置了索引。但是,MySQL解释显示它正在合并索引:
Using intersect(user_id,ref_url); Using where
但是为什么它不按照它们的实际列顺序(ref_url,user_id)这样做呢?另外,添加索引(ref_url
这是我的试验:
mysql> select a.id from iask a
-> join ianswer b on a.id=b.iaskid
-> join users c on c.id=a.uid
-> where (c.last_check is null or a.created>c.last_check) and c.id=1
->
我想做的是索引一个人的名字和出生日期。
这张桌子的排列方式如下:
CREATE TABLE test
(
id INT NOT NULL AUTO_INCREMENT,
fname VARCHAR(10) NOT NULL,
sname VARCHAR(10) NOT NULL,
age INT NOT NULL,
born DATETIME NOT NULL,
PRIMARY KEY(id),
INDEX name_age(fname, sname, age),
INDEX name_date(fname, born)
)
但是,在where语句中没有识别该索引,如:
my
我尽了最大努力解决以下两个简单查询,但对于每个10行结果集,它将扫描整个表或至少10K行。目前,books表中有20000行。
ALTER TABLE books ADD INDEX search_INX (`book_status`, `is_reviewed`,`has_image`,`published_date`)
mysql> EXPLAIN SELECT book_id FROM books ORDER BY published_date DESC LIMIT 10;
+----+-------------+-------+-------+---------------+-
我使用MySQL 5.5创建了这个MySQL表:
mysql> CREATE TEMPORARY TABLE IF NOT EXISTS TableOne
-> (high INT NOT NULL, low INT NOT NULL, current INT NULL,
-> INDEX connect (low, current), INDEX theHigh (high), INDEX theCurrent (current));
Query OK, 0 rows affected (0.11 sec)
然后我测试了两个查询:
mysql> de
说我有这样的疑问:
EXPLAIN SELECT *
FROM (
SELECT "A" as a, i.n FROM (SELECT 1 AS n) AS i
UNION ALL SELECT "B" as a, i.n FROM (SELECT 1 AS n) AS i) AS t
WHERE a = "B";
MySQL说
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
我有两个问题,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
和
select some_other_column
from `table`
order by secondary_index_column asc
limit 4000000, 10;
两者都返回10行;第一行花费2.74秒,第二行花费7.07秒。some_other_column不是任何索引的一部分。primary_index_column是主键列;secondary_index_
我希望获得具有sec_id的行数,并且ref_id应该出现在列表中,比如ref_list。ref_list的大小约为300个。桌子的大小约为70,000。
我目前正在使用这种技术来获得满足上述条件的行数。
SELECT count(*) FROM table where sec_id=? and ref_id IN (?,?,...,?)
它的执行速度在20到30 is之间。
但是这个查询将在大约1200+时间内执行,所以执行的总时间大约是45到50。
还有其他方法可以对上面的查询进行修改,以便减少时间吗?
该表的索引使用id作为primary key,而键类型的ref_id作为多个
数据库-
当我在一个包含2200万行的表上运行以下查询时,它需要20秒才能运行:
select p.*,
(select avg(close)
from endOfDayData p2
where p2.symbol = p.symbol and
p2.date between p.date - interval 6 day and p.date
) as MvgAvg_X
from endOfDayData p
where p.symbol = 'AAPL'
表结构如下:
mysql> desc endOfDayData;
+--------+---------------+--
假设我有一个“子项”表,其中有以下列:
article_text: fulltext indexed
author_id: indexed
现在,我想寻找一个术语,出现在一篇文章中,一个特定的艺术已经写。
所以,就像:
select * from articles
where author_id=54
and match (article_text) against ('foo');
这个查询的解释告诉我mysql只会使用全文索引。我相信mysql只能使用一个索引,但是在全文搜索这个词之前,让特定作者首先写的所有文章似乎是一个明智的主意。那么还有什么可以帮助mys
在超越MySQL基础知识的时候,我并不聪明,但是,我试图优化一个查询:
SELECT DATE_FORMAT(t.completed, '%H') AS hour, t.orderId, t.completed as stamp,
t.deadline as deadline, t.completedBy as user, p.largeFormat as largeFormat
FROM tasks t
JOIN orders o ON o.id=t.orderId
JOIN products p ON p.id=o.productId
让MySQL将索引()放在正确的字段上有点困难。我有一个查询,例如,如下所示:
SELECT m.id as mid,p.id as pid,po.* FROM m
LEFT OUTER JOIN p ON p.mid = m.id AND p.uid = 2
LEFT OUTER JOIN po ON po.mid = m.id AND po.uid = 2
ORDER BY m.date,m.time
我有以下索引:
m.timep.midp.uidpo.midpo.uid m.date m.date
但是,当我运行一个EXPLAIN查询时,没有一个键被选中。我试图将FORCE IN