我正在努力解决慢查询问题。我正在运行 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
解决方案跳过 - 请再次检查？ 之后的所有内容''where'' 以及用于连接的所有列。 你确定你已经将这些列编入索引吗？ a手动检查很有帮助。 mondo问候[比尔] - William Sanders /电子归档组删除DOT BOB以通过 电子邮件回复。 免费长距离 - > mailto：ex ******** @ efgroup 免费卫星接收器和安装 - > www.vmcsatellite/?aid=58456 mySql / VFP / MS-SQL " Skip Montanaro" < SK ** @ pobox>在消息中写道 news：72 ************************* @ posting.google.co m ... 我正在努力解决慢查询问题。我正在跑步 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
跳过 - 请再次检查？ 之后的所有内容''where'' 以及用于连接的所有列。 你确定你已将这些列编入索引吗？ a手动检查很有帮助。 mondo问候[比尔] - William Sanders /电子归档组删除DOT BOB通过 电子邮件回复。 免费长距离 - > mailto：ex ******** @ efgroup 免费卫星接收器和安装 - > www.vmcsatellite/?aid=58456 mySql / VFP / MS-SQL " Skip Montanaro" < SK ** @ pobox>在消息中写道 news：72 ************************* @ posting.google.co m ... 我正在努力解决慢查询问题。我正在跑步 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
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
解决方案 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 Free Satellite Receivers and installation -> www.vmcsatellite/?aid=58456 mySql / VFP / MS-SQL "Skip Montanaro" <sk**@pobox> 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 andevents.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
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 Free Satellite Receivers and installation -> www.vmcsatellite/?aid=58456 mySql / VFP / MS-SQL "Skip Montanaro" <sk**@pobox> 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 andevents.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
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.328130574Why 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忘了它有索引吗？
发布评论