MySQL忘了它有索引吗？ [英] MySQL forgets it has indexes?
问题描述
我正在努力解决慢查询问题。我正在运行
3.23.41。这是慢速日志文件中的条目。
＃时间：031006 15:15:43
＃User @ Host：音乐会[音乐会] @ localhost []
＃时间：173 Lock_time：58 Rows_sent：14 Rows_examined：361827
select events.id，events.start，events.end from城市straight_join
地址为straight_join场地，事件，关键字，其中cities.latitude< =
39.6940816812和cities.longitude< = -76.444647426和cities.latitude
= 38.2448063188和cities.longitude> = -78.328130574和events.venue
= venues.id和venues.address = addresses.id和addresses.city =
cities.id和events.end> =''2003-10-06''和events.start< =
''2003-11-30''和（keywords.keyword =''traditional''）和keywords.refid =
events.id和keywords.tabletype = 1 and events.submit_date> =
'' 1976-05-20 15:12:50''和events.act ive = 1个订单
events.start，events.end limit 1000;
如果我要求解释，它会告诉我一些不同的东西（粘贴）在
三件以避免80列问题）：
+ ----------- + ------ - + + ---------------------------------- --------- + <无线电通信/>
|表|类型| possible_keys | key |
+ ----------- + -------- + ------------------ ---------------- + --------- +
|关键词| ref | keyword，refid，tabletype |关键字|
|事件| eq_ref | id，submit_date，场地，日期，活动| id |
|城市|范围| id，latlong | latlong |
|地址| ref | id，城市|城市|
|场地| eq_ref | id，地址| id |
+ ----------- + -------- + ------------------ ---------------- + --------- +
+ --------- + ---------------- + ------ +
| key_len | ref |行|
+ --------- + ---------------- + ------ +
| 32 | const | 841 |
| 4 | keywords.refid | 1 |
| 8 | NULL | 945 |
| 4 | cities.id | 16 |
| 4 | events.venue | 1 |
+ --------- + ---------------- + ------ +
+ ------------------------------------------ --- +
|额外的|
+ --------------------------------------- ------ +
|使用的地方;使用临时;使用filesort |
|使用的地方|
|使用的地方|
| |
|使用的地方|
+ -------------------------------------- ------- +
请注意它检查了几十万行，虽然解释
输出表明它应该检查少于2000.它'好像MySQL
忘了它有索引可以咨询。有没有办法确认这个或
强制它重建索引？
谢谢，
Skip蒙塔纳罗
sk**@pobox.com
跳过 - 请再次检查？
之后的所有内容''where''
以及用于连接的所有列。
你确定你已经将这些列编入索引吗？
a手动检查很有帮助。
mondo问候[比尔]
-
William Sanders /电子归档组删除DOT BOB以通过
电子邮件回复。
免费长距离 - > mailto：ex ******** @ efgroup.net
免费卫星接收器和安装 - >
http://www.vmcsatellite.com/?aid=58456
mySql / VFP / MS-SQL
" Skip Montanaro" < SK ** @ pobox.com>在消息中写道
news：72 ************************* @ posting.google.co m ... < blockquote class =post_quotes>我正在努力解决慢查询问题。我正在跑步
3.23.41。这是慢速日志文件中的一个条目。
＃时间：031006 15:15:43
＃User @ Host：音乐会[音乐会] @ localhost []
＃时间：173 Lock_time：58 Rows_sent：14 Rows_examined：361827
选择events.id，events.start，events.end from cities straight_join
地址为straight_join场地，事件，城市关键词。纬度
< = 39.6940816812和cities.longitude< = -76.444647426和
cities.latitude> = 38.2448063188和cities.longitude > = -78.328130574和
events.venue = venues.id和venues.address = addresses.id和addresses.city =
cities.id和events.end> =''2003-10-06''和events.start< =
''2003-11-30''和（keywords.keyword =''traditional''）和关键字。 refid
= events.id和keywords.tabletype = 1和events.submit_date> =
''1976-05-20 15:12:50''和events.active = 1个订单来自
events.start，events.end limit 1000;
如果我要求解释，它告诉我一些不同的东西（为了避免80列问题，分三个部分粘贴
）：
+ ----------- + ----- --- + + ---------------------------------- --------- +
|表|类型| possible_keys | key |
+ ----------- + -------- + ---------------------- ------------ + --------- +
|关键词| ref | keyword，refid，tabletype |关键字|
|事件| eq_ref | id，submit_date，场地，日期，活动| id |
|城市|范围| id，latlong | latlong |
|地址| ref | id，城市|城市|
|场地| eq_ref | id，地址| id |
+ ----------- + -------- + ---------------------- ------------ + --------- +
+ --------- + -------- -------- + ------ +
| key_len | ref |行|
+ --------- + ---------------- + ------ +
| 32 | const | 841 |
| 4 | keywords.refid | 1 |
| 8 | NULL | 945 |
| 4 | cities.id | 16 |
| 4 | events.venue | 1 |
+ --------- + ---------------- + ------ +
+ --------------------------------------------- +
|额外的|
+ ------------------------------------------- - +
|使用的地方;使用临时;使用filesort |
|使用的地方|
|使用的地方|
| |
|使用的地方|
+ ------------------------------------------ --- +
请注意它检查了几十万行，虽然解释
输出表明它应该检查少于2000.这就好像MySQL已经忘记它了索引咨询。有没有办法确认这个或
迫使它重建索引？
谢谢，
Skip Montanaro
sk ** @ pobox.com
跳过 - 请再次检查？
之后的所有内容''where''
以及用于连接的所有列。
你确定你已将这些列编入索引吗？
a手动检查很有帮助。
mondo问候[比尔]
-
William Sanders /电子归档组删除DOT BOB通过
电子邮件回复。
免费长距离 - > mailto：ex ******** @ efgroup.net
免费卫星接收器和安装 - >
http://www.vmcsatellite.com/?aid=58456
mySql / VFP / MS-SQL
" Skip Montanaro" < SK ** @ pobox.com>在消息中写道
news：72 ************************* @ posting.google.co m ... < blockquote class =post_quotes>我正在努力解决慢查询问题。我正在跑步
3.23.41。这是慢速日志文件中的一个条目。
＃时间：031006 15:15:43
＃User @ Host：音乐会[音乐会] @ localhost []
＃时间：173 Lock_time：58 Rows_sent：14 Rows_examined：361827
选择events.id，events.start，events.end from cities straight_join
地址为straight_join场地，事件，城市关键词。纬度
< = 39.6940816812和cities.longitude< = -76.444647426和
cities.latitude> = 38.2448063188和cities.longitude > = -78.328130574和
events.venue = venues.id和venues.address = addresses.id和addresses.city =
cities.id和events.end> =''2003-10-06''和events.start< =
''2003-11-30''和（keywords.keyword =''traditional''）和关键字。 refid
= events.id和keywords.tabletype = 1和events.submit_date> =
''1976-05-20 15:12:50''和events.active = 1个订单来自
events.start，events.end limit 1000;
如果我要求解释，它告诉我一些不同的东西（为了避免80列问题，分三个部分粘贴
）：
+ ----------- + ----- --- + + ---------------------------------- --------- +
|表|类型| possible_keys | key |
+ ----------- + -------- + ---------------------- ------------ + --------- +
|关键词| ref | keyword，refid，tabletype |关键字|
|事件| eq_ref | id，submit_date，场地，日期，活动| id |
|城市|范围| id，latlong | latlong |
|地址| ref | id，城市|城市|
|场地| eq_ref | id，地址| id |
+ ----------- + -------- + ---------------------- ------------ + --------- +
+ --------- + -------- -------- + ------ +
| key_len | ref |行|
+ --------- + ---------------- + ------ +
| 32 | const | 841 |
| 4 | keywords.refid | 1 |
| 8 | NULL | 945 |
| 4 | cities.id | 16 |
| 4 | events.venue | 1 |
+ --------- + ---------------- + ------ +
+ --------------------------------------------- +
|额外的|
+ ------------------------------------------- - +
|使用的地方;使用临时;使用filesort |
|使用的地方|
|使用的地方|
| |
|使用的地方|
+ ------------------------------------------ --- +
请注意它检查了几十万行，虽然解释
输出表明它应该检查少于2000.这就好像MySQL已经忘记它了索引咨询。有没有办法确认这个或
迫使它重建索引？
谢谢，
Skip Montanaro
sk ** @ pobox.com
Skip Montanaro写道：
我正在努力解决慢查询问题。我正在跑步
3.23.41。这是慢速日志文件中的一个条目。
＃时间：031006 15:15:43
＃User @ Host：音乐会[音乐会] @ localhost []
＃时间：173 Lock_time：58 Rows_sent：14 Rows_examined：361827
选择events.id，events.start，events.end来自城市straight_join
地址straight_join场地，事件，关键字
其中cities.latitude< = 39.6940816812和cities.longitude< =
-76.444647426
和
cities.latitude> = 38.2448063188和cities.longitude> = -78.328130574
为什么你问纬度< = 39，然后别的东西，然后回去做
范围搜索有纬度？
你问的是ALL< = 39，然后再做一些其他事情。然后你问ALL = 38.然后你问这两个大组的常见行。
在最坏的情况下，数据库确实按照你的要求行事。
I''m struggling to get a handle on a slow query problem. I''m running
3.23.41. Here''s an entry from the slow log file.
# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827
select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords where cities.latitude <=
39.6940816812 and cities.longitude <= -76.444647426 and cities.latitude
= 38.2448063188 and cities.longitude >= -78.328130574 and events.venue
= venues.id and venues.address = addresses.id and addresses.city =
cities.id and events.end >= ''2003-10-06'' and events.start <=
''2003-11-30'' and (keywords.keyword = ''traditional'') and keywords.refid =
events.id and keywords.tabletype = 1 and events.submit_date >=
''1976-05-20 15:12:50'' and events.active = 1 order by
events.start,events.end limit 1000;
If I ask for an explanation, it tells me something much different (pasted in
three pieces to avoid 80-column problems):
+-----------+--------+----------------------------------+---------+
| table | type | possible_keys | key |
+-----------+--------+----------------------------------+---------+
| keywords | ref | keyword,refid,tabletype | keyword |
| events | eq_ref | id,submit_date,venue,date,active | id |
| cities | range | id,latlong | latlong |
| addresses | ref | id,city | city |
| venues | eq_ref | id,address | id |
+-----------+--------+----------------------------------+---------+
+---------+----------------+------+
| key_len | ref | rows |
+---------+----------------+------+
| 32 | const | 841 |
| 4 | keywords.refid | 1 |
| 8 | NULL | 945 |
| 4 | cities.id | 16 |
| 4 | events.venue | 1 |
+---------+----------------+------+
+---------------------------------------------+
| Extra |
+---------------------------------------------+
| where used; Using temporary; Using filesort |
| where used |
| where used |
| |
| where used |
+---------------------------------------------+
Note that it examines several hundred thousand rows although the explain
output suggests it should examine fewer than 2000. It''s as if MySQL
forgot it had indexes to consult. Is there some way to confirm this or
force it to rebuild its indexes?
Thanks,
Skip Montanaro
sk**@pobox.com
Skip - please check again ??
everything AFTER the word ''where''
and all of your columns used for joins .
are you certain you have these columns indexed ?
a manual check is helpful.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
Free Satellite Receivers and installation ->
http://www.vmcsatellite.com/?aid=58456
mySql / VFP / MS-SQL
"Skip Montanaro" <sk**@pobox.com> wrote in message
news:72*************************@posting.google.co m...I''m struggling to get a handle on a slow query problem. I''m running
3.23.41. Here''s an entry from the slow log file.
# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827
select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords where cities.latitude <= 39.6940816812 and cities.longitude <= -76.444647426 and cities.latitude>= 38.2448063188 and cities.longitude >= -78.328130574 and
events.venue = venues.id and venues.address = addresses.id and addresses.city =
cities.id and events.end >= ''2003-10-06'' and events.start <=
''2003-11-30'' and (keywords.keyword = ''traditional'') and keywords.refid = events.id and keywords.tabletype = 1 and events.submit_date >=
''1976-05-20 15:12:50'' and events.active = 1 order by
events.start,events.end limit 1000;
If I ask for an explanation, it tells me something much different (pasted in three pieces to avoid 80-column problems):
+-----------+--------+----------------------------------+---------+
| table | type | possible_keys | key |
+-----------+--------+----------------------------------+---------+
| keywords | ref | keyword,refid,tabletype | keyword |
| events | eq_ref | id,submit_date,venue,date,active | id |
| cities | range | id,latlong | latlong |
| addresses | ref | id,city | city |
| venues | eq_ref | id,address | id |
+-----------+--------+----------------------------------+---------+
+---------+----------------+------+
| key_len | ref | rows |
+---------+----------------+------+
| 32 | const | 841 |
| 4 | keywords.refid | 1 |
| 8 | NULL | 945 |
| 4 | cities.id | 16 |
| 4 | events.venue | 1 |
+---------+----------------+------+
+---------------------------------------------+
| Extra |
+---------------------------------------------+
| where used; Using temporary; Using filesort |
| where used |
| where used |
| |
| where used |
+---------------------------------------------+
Note that it examines several hundred thousand rows although the explain
output suggests it should examine fewer than 2000. It''s as if MySQL
forgot it had indexes to consult. Is there some way to confirm this or
force it to rebuild its indexes?
Thanks,
Skip Montanaro
sk**@pobox.com
Skip - please check again ??
everything AFTER the word ''where''
and all of your columns used for joins .
are you certain you have these columns indexed ?
a manual check is helpful.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
Free Satellite Receivers and installation ->
http://www.vmcsatellite.com/?aid=58456
mySql / VFP / MS-SQL
"Skip Montanaro" <sk**@pobox.com> wrote in message
news:72*************************@posting.google.co m...I''m struggling to get a handle on a slow query problem. I''m running
3.23.41. Here''s an entry from the slow log file.
# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827
select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords where cities.latitude <= 39.6940816812 and cities.longitude <= -76.444647426 and cities.latitude>= 38.2448063188 and cities.longitude >= -78.328130574 and
events.venue = venues.id and venues.address = addresses.id and addresses.city =
cities.id and events.end >= ''2003-10-06'' and events.start <=
''2003-11-30'' and (keywords.keyword = ''traditional'') and keywords.refid = events.id and keywords.tabletype = 1 and events.submit_date >=
''1976-05-20 15:12:50'' and events.active = 1 order by
events.start,events.end limit 1000;
If I ask for an explanation, it tells me something much different (pasted in three pieces to avoid 80-column problems):
+-----------+--------+----------------------------------+---------+
| table | type | possible_keys | key |
+-----------+--------+----------------------------------+---------+
| keywords | ref | keyword,refid,tabletype | keyword |
| events | eq_ref | id,submit_date,venue,date,active | id |
| cities | range | id,latlong | latlong |
| addresses | ref | id,city | city |
| venues | eq_ref | id,address | id |
+-----------+--------+----------------------------------+---------+
+---------+----------------+------+
| key_len | ref | rows |
+---------+----------------+------+
| 32 | const | 841 |
| 4 | keywords.refid | 1 |
| 8 | NULL | 945 |
| 4 | cities.id | 16 |
| 4 | events.venue | 1 |
+---------+----------------+------+
+---------------------------------------------+
| Extra |
+---------------------------------------------+
| where used; Using temporary; Using filesort |
| where used |
| where used |
| |
| where used |
+---------------------------------------------+
Note that it examines several hundred thousand rows although the explain
output suggests it should examine fewer than 2000. It''s as if MySQL
forgot it had indexes to consult. Is there some way to confirm this or
force it to rebuild its indexes?
Thanks,
Skip Montanaro
sk**@pobox.com
Skip Montanaro wrote:
I''m struggling to get a handle on a slow query problem. I''m running
3.23.41. Here''s an entry from the slow log file.
# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827
select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords
where cities.latitude <= 39.6940816812 and cities.longitude <= -76.444647426
and
cities.latitude>= 38.2448063188 and cities.longitude >= -78.328130574
Why do you ask latitude <= 39 , then something else, and then go back doing
range search with latitude?
What you are asking is ALL <= 39, then do something else. Then you ask ALL= 38. Then you ask common rows from these two big groups.
In worst case the database really does what you ask.
这篇关于MySQL忘了它有索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
