主页 / user-2613

user4951's questions

Martin Hope
user4951
Asked: 2012-09-07 22:20:34 +0800 CST
  • 1

我注意到 $near 和 $nearSphere 返回的数据完全不同，我对此产生了怀疑。毕竟，当纬度接近赤道时，球面距离与欧几里得距离应该相差不大。

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^soto/, /^nasi/] } }).limit(200);

它返回 48 个数据。

反正我会附上

/* 0 */
{
  "_id" : "nasi-uduk-soto-ayam__-6.18_106.77",
  "BuildingID" : null,
  "Title" : "Nasi Uduk Soto Ayam",
  "InBuildingAddress" : null,
  "Building" : null,
  "Street" : "Jl. Panjang",
  "Districts" : [],
  "City" : "Jakarta",
  "Country" : "Indonesia",
  "LongitudeLatitudeFromGoogle" : null,
  "DistanceFromGoogleAddress" : 0.0,
  "Checkin" : 0,
  "Note" : null,
  "PeopleCount" : 0,
  "Prominent" : 45.5,
  "CountViews" : 0,
  "StreetAdditional" : null,
  "LongitudeLatitude" : {
    "Longitude" : 106.765673160553,
    "Latitude" : -6.17522230915668
  },
  "Rating" : {
    "Stars" : 0.0,
    "Weight" : 0.0
  },
  "CurrentlyWorkedURL" : null,
  "Reviews" : [],
  "ZIP" : null,
  "Tags" : ["Restaurant"],
  "Phones" : [],
  "Website" : null,
  "Email" : null,
  "Price" : null,
  "openingHour" : null,
  "Promotions" : [],
  "SomethingWrong" : false,
  "BizMenus" : [],
  "Brochures" : [],
  "Aliases" : [],
  "indexContents" : ["restaura", "estauran", "staurant", "taurant", "aurant", "urant", "rant", "ant", "nt", "t", "nasi", "asi", "si", "i", "uduk", "duk", "uk", "k", "soto", "oto", "to", "o", "ayam", "yam", "am", "m"]
}

/* 1 */
{
  "_id" : "nasi-soto-padang_pasar-slipi-jaya_-6.19_106.80",
  "BuildingID" : null,
  "Title" : "Nasi Soto Padang",
  "InBuildingAddress" : "Lt.1 Los Alas",
  "Building" : null,
  "Street" : "Jl.Kemanggisan Utama Raya",
  "Districts" : [],
  "City" : "Jakarta",
  "Country" : "Indonesia",
  "LongitudeLatitudeFromGoogle" : null,
  "DistanceFromGoogleAddress" : 0.0,
  "Checkin" : 0,
  "Note" : null,
  "PeopleCount" : 0,
  "Prominent" : 45.5,
  "CountViews" : 0,
  "StreetAdditional" : null,
  "LongitudeLatitude" : {
    "Longitude" : 106.79647564888,
    "Latitude" : -6.18998465381734
  },
  "Rating" : {
    "Stars" : 0.0,
    "Weight" : 0.0
  },
  "CurrentlyWorkedURL" : null,
  "Reviews" : [],
  "ZIP" : null,
  "Tags" : ["Restaurant"],
  "Phones" : [],
  "Website" : null,
  "Email" : null,
  "Price" : null,
  "openingHour" : null,
  "Promotions" : [],
  "SomethingWrong" : false,
  "BizMenus" : [],
  "Brochures" : [],
  "Aliases" : [],
  "indexContents" : ["restaura", "estauran", "staurant", "taurant", "aurant", "urant", "rant", "ant", "nt", "t", "nasi", "asi", "si", "i", "soto", "oto", "to", "o", "padang", "adang", "dang", "ang", "ng", "g"]
}

...

/* 47 */
{
  "_id" : "nasi-gandul__-7.43_109.24",
  "BuildingID" : null,
  "Title" : "Nasi Gandul",
  "InBuildingAddress" : null,
  "Building" : null,
  "Street" : "Jl. Brobahan Pr - 40 rt 004 Rw 004",
  "Districts" : [],
  "City" : "Purwokerto",
  "Country" : "Indonesia",
  "LongitudeLatitudeFromGoogle" : null,
  "DistanceFromGoogleAddress" : 0.0,
  "Checkin" : 0,
  "Note" : null,
  "PeopleCount" : 0,
  "Prominent" : 30.5,
  "CountViews" : 0,
  "StreetAdditional" : null,
  "LongitudeLatitude" : {
    "Longitude" : 109.239182174206,
    "Latitude" : -7.42585664273589
  },
  "Rating" : {
    "Stars" : 3.0,
    "Weight" : 1.0
  },
  "CurrentlyWorkedURL" : null,
  "Reviews" : [],
  "ZIP" : "53116",
  "Tags" : ["Angkringan", "Restaurant Indonesian", "Soto & Sop"],
  "Phones" : ["+62(281)7918181"],
  "Website" : null,
  "Email" : null,
  "Price" : null,
  "openingHour" : null,
  "Promotions" : [],
  "SomethingWrong" : false,
  "BizMenus" : [],
  "Brochures" : [],
  "Aliases" : [],
  "indexContents" : ["angkring", "ngkringa", "gkringan", "kringan", "ringan", "ingan", "ngan", "gan", "an", "n", "restaura", "estauran", "staurant", "taurant", "aurant", "urant", "rant", "ant", "nt", "t", "indonesi", "ndonesia", "donesian", "onesian", "nesian", "esian", "sian", "ian", "soto", "oto", "to", "o", "&", "sop", "op", "p", "nasi", "asi", "si", "i", "gandul", "andul", "ndul", "dul", "ul", "l"]
}

我将数据放入excel并使用此公式计算距离

=SQRT(POWER(E2-$G$1,2)+POWER((F2-$H$1)*COS($H$1*PI()/180),2))

那应该大大接近球面距离。另请注意，纬度为-6，距离赤道不远。

这是结果

0.013516826
0.023857967
0.037658667
0.038737146
0.042414787
0.046725248
0.051006427
0.053567221
0.057448344
0.061592999
0.062329244
0.065276161
0.066035611
0.076251787
0.109671831
0.112097201
0.13417281
0.136471939
0.172293693
1.058802838
1.078123028
1.079160684
1.080954023
1.081148114
1.081099449
1.092061283
1.094281476
1.094431917
1.096845722
1.097063729
1.096953691
1.097201996
1.105389179
1.105442127
1.10839237
1.108717834
1.108840349
1.111636423
1.113187903
1.118767984
1.118767984
1.133952371
1.135077548
1.154967917
1.161142923
1.185994885
1.199509086
2.756884824

这是我的excel表格的截图在此处输入图像描述

这是实际的excel

            106.772835  -6.186753
  "_id"      "nasi-uduk-soto-ayam__-6.18_106.77",   106.7656732 -6.175222309    0.013516826
  "_id"      "nasi-soto-padang_pasar-slipi-jaya_-6.19_106.80",  106.7964756 -6.189984654    0.023857967
  "_id"      "nasi-uduk-soto-ayam__-6.22_106.77",   106.7718959 -6.224620499    0.037658667
  "_id"      "nasi-campur-%26-soto-babat-kenanga_komp.-ruko-permata-senayan%2C_-6.22_106.79",   106.79199   -6.22062    0.038737146
  "_id"      "soto-mie-nasi_indomaret-(univ.-mercu-buana)_-6.21_106.74",    106.7379928 -6.211082144    0.042414787
  "_id"      "soto-kudus%2C-soto-ayam-%26amp%3B-nasi-pindang_plaza-senayan_-6.23_106.80",   106.7989969 -6.225694167    0.046725248
  "_id"      "soto-kudus%2C-soto-ayam-%26amp%3B-nasi-pindang_plaza-bri-ii_-6.22_106.81",    106.8136847 -6.217476287    0.051006427
  "_id"      "soto-kudus%2C-soto-ayam-%26amp%3B-nasi-pindang_automall-indonesia_-6.23_106.81",  106.8096431 -6.225898946    0.053567221
  "_id"      "soto-kudus%2C-soto-ayam-%26amp%3B-nasi-pindang_menara-mulia_-6.22_106.82",    106.81656   -6.224232983    0.057448344
  "_id"      "nasi-gule-gandaria__-6.24_106.80",    106.795285  -6.244444863    0.061592999
  "_id"      "nasi-soto-monggo-mampir__-6.24_106.80",   106.7971516 -6.244479441    0.062329244
  "_id"      "nasi-soto-ayam-\"pak-min\"__-6.24_106.82",    106.8151546 -6.236743573    0.065276161
  "_id"      "nasi-soto-ayam-pak-min---santa__-6.24_106.81",    106.812959  -6.239508   0.066035611
  "_id"      "soto-mie-nasi-bogor__-6.23_106.83",   106.8322349 -6.234845139    0.076251787
  "_id"      "nasi-uduk-soto-ayam__-6.24_106.87",   106.8693781 -6.239089942    0.109671831
  "_id"      "soto-ayam-%2F-nasi-uduk-betawi__-6.28_106.71",    106.714092  -6.282785   0.112097201
  "_id"      "soto-minang-roda-jaya_ruko-tongkol-indah_-6.12_106.89",   106.889044  -6.119294471    0.13417281
  "_id"      "nasi-bebek-dan-soto-mas-muchlis_summarecon-kelapa-gading_-6.16_106.91",   106.9059021 -6.156281135    0.136471939
  "_id"      "nasi-liwet-%26-soto-kwali-kalimalang__-6.25_106.93",  106.9338112 -6.248527508    0.172293693
  "_id"      "soto-ayam-nasi-rames__-6.88_107.58",  107.5751638 -6.881692483    1.058802838
  "_id"      "nasi-liwet-soto-solo_rumah-mode_-6.88_107.60",    107.5998616 -6.882452292    1.078123028
  "_id"      "nasi-soto-ayam-madura__-6.91_107.57", 107.5749707 -6.912900474    1.079160684
  "_id"      "nasi-soto-mie__-6.89_107.60", 107.5969434 -6.890355658    1.080954023
  "_id"      "nasi-rames-soto-bandung-enjoi__-6.89_107.60", 107.5970721 -6.890504777    1.081148114
  "_id"      "nasi-soto-ayam__-6.91_107.58",    107.5769877 -6.913560825    1.081099449
  "_id"      "nasi-soto-ayam__-6.91_107.60",    107.5977552 -6.90656    1.092061283
  "_id"      "nasi-soto-ayam-madura__-6.89_107.62", 107.6157832 -6.888615935    1.094281476
  "_id"      "nasi-goreng-soto-ayam__-6.90_107.61", 107.6093245 -6.89662571 1.094431917
  "_id"      "nasi-soto-gulai-kambing__-6.90_107.61",   107.6090455 -6.900715755    1.096845722
  "_id"      "putra-bengawan-nasi-goreng-%26-soto__-6.89_107.62",   107.617187  -6.891298   1.097063729
  "_id"      "nasi-soto-ayam__-6.90_107.61",    107.6088095 -6.901163102    1.096953691
  "_id"      "nasi-uduk-%26amp%3B-soto-kikil-cak-khohar__-6.89_107.61", 107.6144958 -6.894765295    1.097201996
  "_id"      "nasi-soto-ayam__-6.90_107.62",    107.6204395 -6.900453028    1.105389179
  "_id"      "nasi-goreng-soto-ayam__-6.90_107.62", 107.6206326 -6.900303912    1.105442127
  "_id"      "nasi-soto-ayam-madura__-6.91_107.62", 107.6169634 -6.909279174    1.10839237
  "_id"      "nasi-soto_pasar-baru_-6.92_107.61",   107.605226  -6.923423387    1.108717834
  "_id"      "nasi-soto-ayam-madura_pasar-baru_-6.92_107.61",   107.6053333 -6.92348729 1.108840349
  "_id"      "nasi-soto-sarinah__-6.90_107.63", 107.631175  -6.897282   1.111636423
  "_id"      "nasi-uduk-soto__-6.89_107.64",    107.6361465 -6.893636262    1.113187903
  "_id"      "nasi-soto-ayam-madura__-6.91_107.63", 107.629376  -6.910677   1.118767984
  "_id"      "nasi-soto-ayam__-6.91_107.63",    107.629376  -6.910677   1.118767984
  "_id"      "nasi-uduk-soto__-6.94_107.62",    107.6248813 -6.93937772 1.133952371
  "_id"      "nasi-soto-ayam-khas-madura__-6.94_107.63",    107.625749  -6.940099   1.135077548
  "_id"      "nasi-uduk-%26amp%3B-soto__-6.96_107.64",  107.638936  -6.955310213    1.154967917
  "_id"      "nasi-soto-ayam_ruko-metro-trade-center_-6.94_107.66", 107.6600504 -6.940208438    1.161142923
  "_id"      "nasi-soto-ayam-khas-madura__-6.94_107.69",    107.6948118 -6.937141164    1.185994885
  "_id"      "nasi-soto-ayam-khas-madura-cak-nonk__-6.93_107.72",   107.7156472 -6.932668019    1.199509086
  "_id"      "nasi-gandul__-7.43_109.24",   109.2391822 -7.425856643    2.756884824

再次查看查询中的术语

"$maxDistance" : 0.053980478460939611

去搞清楚。

mongodb spatial
Martin Hope
user4951
Asked: 2012-09-05 23:23:25 +0800 CST
  • 3
database.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } ntoreturn:20 ntoskip:80 nscanned:100 nreturned:20 reslen:1147 1927ms

似乎 ntoreturn 意味着要返回的数字。ntoskip 是要跳过的数字。nscanned 似乎是 2 的总和。

什么是reslen？

我搜索谷歌没有答案。什么网站解释什么是reslen？

mongodb
Martin Hope
user4951
Asked: 2012-09-05 22:00:28 +0800 CST
  • 0

如果有，是哪一个？

我们怎么知道？

我在一个查询中有 3 个索引。实际上只有前两个应该有用

{
   "v": NumberInt(1),
   "key": {
     "LongitudeLatitude": "2d",
     "Prominent"▼: NumberInt(-1),
     "indexContents": NumberInt(1) 
  },
   "ns": "database.tablebusiness",
   "name": "LongLat_Prominent_indexContents",
   "dropDups": false,
   "background": false 
}

{
   "v": NumberInt(1),
   "key": {
     "LongitudeLatitude": "2d",
     "Prominent": NumberInt(-1) 
  },
   "ns": "database.tablebusiness",
   "name": "LongLat_Prominent",
   "dropDups": false,
   "background": false 
}

这个不是必须的：

{
   "v": NumberInt(1),
   "key": {
     "indexContents": NumberInt(1) 
  },
   "ns": "database.tablebusiness",
   "name": "indexContents",
   "dropDups": false,
   "background": false 
}

现在我做这个查询

（查询来自 PhP，所以我只粘贴我在 mongodb 日志和 mongovue 中看到的内容

database.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } ntoreturn:20 ntoskip:80 nscanned:100 nreturned:20 reslen:1147 1927ms

现在复制{ LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } }到 mongovue find 命令让我得到真正的数据库命令

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^op/] } }).limit(50);

这已经足够好了（除了限制稍微改变了一点）。在 mongo.exe 中做解释我得到了

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^op/] } }).limit(50).explain();

{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 50,
        "nscannedObjects" : 50,
        "n" : 50,
        "millis" : 3291,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {

        }
}

现在基于此，使用什么索引？mongodb 是否使用任何索引？任何有关如何改进此查询的提示也将不胜感激。

mongodb php
Martin Hope
user4951
Asked: 2012-08-31 03:37:29 +0800 CST
  • 5

我已经更改了要使用的目录的所有者

root@ip-10-138-30-205:/media/newdrive# ls -l                                                                                                                                                                                     
total 20
drwxr-xr-x 3 mongodb root  4096 Aug 30 11:32 data
drwx------ 2 root    root 16384 Aug 30 09:56 lost+found
root@ip-10-138-30-205:/media/newdrive#

我已经重启了mongodb

root@ip-10-138-30-205:/media/newdrive# service mongodb restart
mongodb stop/waiting
mongodb start/running, process 17140
root@ip-10-138-30-205:/media/newdrive#

我的 mongodb.conf 已经更改

# Note: if you run mongodb as a non-root user (recommended) you may
# need to create and set permissions for this directory manually,
# e.g., if the parent directory isn't mutable by the mongodb user.                                                                                                                                                               
dbpath=/media/newdrive/data

#where to log
logpath=/var/log/mongodb/mongodb.log                                                                                                                                                                                             

logappend=true

#port = 27017                                                                                                                                                                                                                    

# Disables write-ahead journaling                                                                                                                                                                                                
# nojournal = true

# Enables periodic logging of CPU utilization and I/O wait

我还应该做什么？我正在使用亚马逊 ec2

更新：我已经知道问题出在哪里，但这里是每条评论的日志。感谢你的回答。

Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1106 { : BinData, : [ "a", "an", "and", "andi", "andid", "andido", "av", "ave", "aven", "aveni", "avenid", "avenida", "c", "ca", "can", "cand", "candi", "candid", "candido", "d", "da", "di", "did", "dido", "do", "e", "ei", "eir", "eira", "en", "eni", "enid", "enida", "i", "id", "ida", "ido", "il", "ilv", "ilve", "ilvei", "ilveir", "ilveira", "ir", "ira", "j", "jo", "jos", "josé", "l", "lv", "lve", "lvei", "lveir", "lveira", "n", "nd", "ndi", "ndid", "ndido", "ni", "nid", "nida", "o", "os", "osé", "r", "ra", "s", "si", "sil", "silv", "silve", "silvei", "silveir", "silveira", "sé", "v", "ve", "vei", "veir", "veira", "ven", "veni", "venid", "venida", "é" ], : 249.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1156 { : BinData, : [ "(", "(p", "(pr", "(pri", "(priv", "(priva", "(privat", "(private", "(private)", ")", "0", "1", "19", "190", "9", "90", "a", "ag", "age", "agem", "at", "ate", "ate)", "c", "co", "con", "cont", "conta", "contag", "contage", "contagem", "e", "e)", "em", "g", "ge", "gem", "h", "ho", "hom", "home", "i", "iv", "iva", "ivat", "ivate", "ivate)", "m", "me", "n", "nt", "nta", "ntag", "ntage", "ntagem", "o", "om", "ome", "on", "ont", "onta", "ontag", "ontage", "ontagem", "p", "pr", "pri", "priv", "priva", "privat", "private", "private)", "r", "ri", "riv", "riva", "rivat", "rivate", "rivate)", "t", "ta", "tag", "tage", "tagem", "te", "te)", "v", "va", "vat", "vate", "vate)" ], : 9.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1134 { : BinData, : [ "(", "(p", "(pr", "(pri", "(priv", "(priva", "(privat", "(private", "(private)", ")", "a", "at", "ate", "ate)", "c", "co", "cov", "covi", "covil", "d", "do", "e", "e)", "h", "ho", "hom", "home", "hos", "hoso", "i", "il", "in", "inh", "inho", "inhos", "inhoso", "iv", "iva", "ivat", "ivate", "ivate)", "l", "m", "me", "n", "nh", "nho", "nhos", "nhoso", "o", "om", "ome", "os", "oso", "ov", "ovi", "ovil", "p", "pr", "pri", "priv", "priva", "privat", "private", "private)", "r", "ri", "riv", "riva", "rivat", "rivate", "rivate)", "s", "so", "t", "te", "te)", "ti", "tin", "tinh", "tinho", "tinhos", "tinhoso", "v", "va", "vat", "vate", "vate)", "vi", "vil" ], : 2.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1111 { : BinData, : [ ",", "a", "ag", "age", "an", "ana", "ar", "ara", "arag", "arage", "as", "at", "ati", "atio", "ation", "c", "ci", "cia", "e", "el", "ell", "ell,", "g", "ga", "gar", "gara", "garag", "garage", "gas", "ge", "h", "he", "hel", "hell", "hell,", "i", "ia", "io", "ion", "l", "l,", "ll", "ll,", "lu", "luc", "luci", "lucia", "n", "na", "o", "on", "or", "os", "ost", "osto", "p", "po", "pos", "post", "posto", "r", "ra", "rag", "rage", "s", "sh", "she", "shel", "shell", "shell,", "st", "sta", "stat", "stati", "statio", "station", "sto", "t", "ta", "tat", "tati", "tatio", "tation", "ti", "tio", "tion", "to", "u", "uc", "uci", "ucia" ], : 16.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 2132 { : BinData, : [ "a", "an", "anc", "anch", "ancho", "anchon", "anchone", "anchonet", "anchonete", "ane", "aneo", "aneou", "aneous", "c", "ce", "cel", "cell", "cella", "cellan", "cellane", "cellaneo", "cellaneou", "cellaneous", "ch", "cho", "chon", "chone", "chonet", "chonete", "d", "do", "e", "el", "ell", "ella", "ellan", "ellane", "ellaneo", "ellaneou", "ellaneous", "eo", "eou", "eous", "er", "et", "ete", "eu", "eul", "eule", "euler", "h", "ho", "hon", "hone", "honet", "honete", "hop", "i", "is", "isc", "isce", "iscel", "iscell", "iscella", "iscellan", "iscellane", "iscellaneo", "iscellaneou", "iscellaneous", "l", "la", "lan", "lanc", "lanch", "lancho", "lanchon", "lanchone", "lanchonet", "lanchonete", "lane", "laneo", "laneou", "laneous", "le", "ler", "ll", "lla", "llan", "llane", "llaneo", "llaneou", "llaneous", "m", "mi", "mis", "misc", "misce", "miscel", "miscell", "miscella", "miscellan", "miscellane", "miscellaneo", "miscellaneou", "miscellaneous", "n", "nc", "nch", "ncho", "nchon", "nchone", "nchonet", "nchonete", "ne", "neo", "neou", "neous", "net", "nete", "o", "on", "one", "onet", "onete", "op", "ou", "ous", "p", "r", "s", "sc", "sce", "scel", "scell", "scella", "scellan", "scellane", "scellaneo", "scellaneou", "scellaneous", "sh", "sho", "shop", "t", "te", "u", "ul", "ule", "uler", "us" ], : 5.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1186 { : BinData, : [ "a", "ac", "aca", "acao", "am", "amp", "ampg", "ampgr", "ampgro", "ampgrou", "ampgroun", "ampground", "an", "ani", "anim", "anima", "animac", "animaca", "animacao", "ao", "as", "asa", "c", "ca", "cam", "camp", "campg", "campgr", "campgro", "campgrou", "campgroun", "campground", "cao", "cas", "casa", "d", "da", "g", "gr", "gro", "grou", "groun", "ground", "i", "im", "ima", "imac", "imaca", "imacao", "m", "ma", "mac", "maca", "macao", "mp", "mpg", "mpgr", "mpgro", "mpgrou", "mpgroun", "mpground", "n", "nd", "ni", "nim", "nima", "nimac", "nimaca", "nimacao", "o", "ou", "oun", "ound", "p", "pg", "pgr", "pgro", "pgrou", "pgroun", "pground", "r", "ro", "rou", "roun", "round", "s", "sa", "u", "un", "und" ], : 1.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1396 { : BinData, : [ "a", "ab", "aba", "abar", "abará", "ar", "ará", "at", "b", "ba", "bar", "bará", "c", "d", "do", "doo", "door", "doors", "e", "ea", "eat", "er", "g", "gr", "gre", "grea", "great", "h", "he", "her", "hi", "his", "hist", "histo", "histor", "histori", "historic", "i", "ic", "is", "ist", "isto", "istor", "istori", "istoric", "it", "ite", "o", "oo", "oor", "oors", "or", "ori", "oric", "ors", "ot", "oth", "othe", "other", "ou", "out", "outd", "outdo", "outdoo", "outdoor", "outdoors", "r", "re", "rea", "reat", "ri", "ric", "rs", "rá", "s", "sa", "sab", "saba", "sabar", "sabará", "si", "sit", "site", "st", "sto", "stor", "stori", "storic", "t", "td", "tdo", "tdoo", "tdoor", "tdoors", "te", "th", "the", "ther", "to", "tor", "tori", "toric", "u", "ut", "utd", "utdo", "utdoo", "utdoor", "utdoors", "á" ], : 318.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1183 { : BinData, : [ "(", "(p", "(pr", "(pri", "(priv", "(priva", "(privat", "(private", "(private)", ")", "a", "ar", "arg", "arge", "argem", "argemi", "argemir", "argemiro", "as", "asa", "at", "ate", "ate)", "c", "ca", "cas", "casa", "d", "de", "e", "e)", "em", "emi", "emir", "emiro", "g", "ge", "gem", "gemi", "gemir", "gemiro", "h", "ho", "hom", "home", "i", "ir", "iro", "iv", "iva", "ivat", "ivate", "ivate)", "m", "me", "mi", "mir", "miro", "o", "om", "ome", "p", "pr", "pri", "priv", "priva", "privat", "private", "private)", "r", "rg", "rge", "rgem", "rgemi", "rgemir", "rgemiro", "ri", "riv", "riva", "rivat", "rivate", "rivate)", "ro", "s", "sa", "t", "te", "te)", "v", "va", "vat", "vate", "vate)" ], : 1.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1232 { : BinData, : [ "a", "ac", "aca", "acad", "acade", "academ", "academi", "academia", "ad", "ade", "adem", "ademi", "ademia", "aç", "aço", "c", "ca", "cad", "cade", "cadem", "cademi", "cademia", "ce", "cen", "cent", "cente", "center", "d", "de", "dem", "demi", "demia", "e", "em", "emi", "emia", "en", "ent", "ente", "enter", "er", "es", "esp", "espa", "espaç", "espaço", "ess", "f", "fi", "fit", "fitn", "fitne", "fitnes", "fitness", "g", "gy", "gym", "i", "ia", "it", "itn", "itne", "itnes", "itness", "m", "mi", "mia", "n", "ne", "nes", "ness", "nt", "nte", "nter", "o", "or", "p", "pa", "paç", "paço", "r", "s", "sp", "spa", "spaç", "spaço", "ss", "t", "te", "ter", "tn", "tne", "tnes", "tness", "y", "ym", "ç", "ço" ], : 1.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1657 { : BinData, : [ "a", "an", "ant", "as", "au", "aur", "aura", "auran", "aurant", "az", "azi", "azil", "azili", "azilia", "azilian", "b", "br", "bra", "braz", "brazi", "brazil", "brazili", "brazilia", "brazilian", "c", "co", "coi", "cois", "coisa", "coisas", "d", "da", "e", "es", "est", "esta", "estau", "estaur", "estaura", "estauran", "estaurant", "i", "ia", "ian", "il", "ili", "ilia", "ilian", "is", "isa", "isas", "l", "li", "lia", "lian", "n", "no", "nos", "noss", "nossa", "nt", "o", "oi", "ois", "oisa", "oisas", "os", "oss", "ossa", "oç", "oça", "r", "ra", "ran", "rant", "raz", "razi", "razil", "razili", "razilia", "razilian", "re", "res", "rest", "resta", "restau", "restaur", "restaura", "restauran", "restaurant", "ro", "roç", "roça", "s", "sa", "sas", "ss", "ssa", "st", "sta", "stau", "staur", "staura", "stauran", "staurant", "t", "ta", "tau", "taur", "taura", "tauran", "taurant", "u", "ur", "ura", "uran", "urant", "z", "zi", "zil", "zili", "zilia", "zilian", "ç", "ça" ], : 5.0 }
Thu Aug 30 17:52:30 [conn6]  isikota Btree::insert: key too large to index, skipping isikota.tablebusiness.$Long_IndexContents_Prominent 1426 { : BinData, : [ "a", "ab", "abe", "an", "ant", "ante", "ar", "ari", "aria", "au", "aur", "aura", "auran", "aurant", "aurante", "b", "be", "c", "cê", "e", "es", "est", "esta", "estau", "estaur", "estaura", "estauran", "estaurant", "estaurante", "i", "ia", "iz", "izz", "izza", "izzar", "izzari", "izzaria", "n", "nt", "nte", "p", "pi", "piz", "pizz", "pizza", "pizzar", "pizzari", "pizzaria", "q", "qu", "qui", "r", "ra", "ran", "rant", "rante", "re", "res", "rest", "resta", "restau", "restaur", "restaura", "restauran", "restaurant", "restaurante", "ri", "ria", "s", "sa", "sab", "sabe", "st", "sta", "stau", "staur", "staura", "stauran", "staurant", "staurante", "t", "ta", "tau", "taur", "taura", "tauran", "taurant", "taurante", "te", "u", "ui", "ur", "ura", "uran", "urant", "urante", "z", "za", "zar", "zari", "zaria", "zz", "zza", "zzar", "zzari", "zzaria", "ê" ], : 24.0 }
Thu Aug 30 17:52:30 [conn6] Assertion: 13524:out of memory AlignedBuilder
0xade6e1 0x8036eb 0x77dadc 0x77db09 0x753da5 0x7540b4 0xa0a2e4 0xa0a83a 0xa082a7 0xa0835c 0xb19666 0xb1e8cc 0xae7c3f 0xae15ed 0xae46e7 0x747ed3 0x7490bd 0x74a9f3 0x665950 0x6571f1 
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0xade6e1]
 /usr/bin/mongod(_ZN5mongo11msgassertedEiPKc+0x9b) [0x8036eb]
 /usr/bin/mongod() [0x77dadc]
 /usr/bin/mongod(_ZN5mongo14AlignedBuilder8_reallocEjj+0x29) [0x77db09]
 /usr/bin/mongod() [0x753da5]
 /usr/bin/mongod(_ZN5mongo3dur13PREPLOGBUFFERERNS0_11JSectHeaderERNS_14AlignedBuilderE+0x214) [0x7540b4]
 /usr/bin/mongod() [0xa0a2e4]
 /usr/bin/mongod(_ZN5mongo3dur11DurableImpl9commitNowEv+0x1a) [0xa0a83a]
 /usr/bin/mongod(_ZN5mongo3dur11DurableImpl16_aCommitIsNeededEv+0x157) [0xa082a7]
 /usr/bin/mongod(_ZN5mongo3dur11DurableImpl14commitIfNeededEb+0x4c) [0xa0835c]
 /usr/bin/mongod(_ZN5mongo12BtreeBuilderINS_12BtreeData_V1EE24mayCommitProgressDurablyEv+0x16) [0xb19666]
 /usr/bin/mongod(_ZN5mongo12BtreeBuilderINS_12BtreeData_V1EE6addKeyERNS_7BSONObjENS_7DiskLocE+0x30c) [0xb1e8cc]
 /usr/bin/mongod(_ZN5mongo24buildBottomUpPhases2And3INS_12BtreeData_V1EEEvbRNS_12IndexDetailsERNS_21BSONObjExternalSorterEbRSt3setINS_7DiskLocESt4lessIS7_ESaIS7_EEPNS_5CurOpEPNS_12SortPhaseOneERNS_19ProgressMeterHolderERNS_5TimerE+0x11f) [0xae7c3f]
 /usr/bin/mongod(_ZN5mongo14fastBuildIndexEPKcPNS_16NamespaceDetailsERNS_12IndexDetailsEi+0xc9d) [0xae15ed]
 /usr/bin/mongod(_ZN5mongo12buildAnIndexESsPNS_16NamespaceDetailsERNS_12IndexDetailsEib+0x217) [0xae46e7]
 /usr/bin/mongod(_ZN5mongo16insert_makeIndexEPNS_16NamespaceDetailsERKSsRKNS_7DiskLocE+0x1f3) [0x747ed3]
 /usr/bin/mongod(_ZN5mongo11DataFileMgr6insertEPKcPKvibbPb+0x93d) [0x7490bd]
 /usr/bin/mongod(_ZN5mongo11DataFileMgr16insertWithObjModEPKcRNS_7BSONObjEb+0x43) [0x74a9f3]
 /usr/bin/mongod(_ZN5mongo10CmdReIndex3runERKSsRNS_7BSONObjEiRSsRNS_14BSONObjBuilderEb+0xf90) [0x665950]
 /usr/bin/mongod(_ZN5mongo12_execCommandEPNS_7CommandERKSsRNS_7BSONObjEiRNS_14BSONObjBuilderEb+0x51) [0x6571f1]
Thu Aug 30 17:52:30 [conn6] dbexception in groupCommit causing immediate shutdown: 13524 out of memory AlignedBuilder
Thu Aug 30 17:52:30 gc1
Thu Aug 30 17:52:31 Got signal: 6 (Aborted).

Thu Aug 30 17:52:31 Backtrace:
0xade6e1 0x5582d9 0x7f60e02cb4c0 0x7f60e02cb445 0x7f60e02cebab 0xb503f7 0xa0a61a 0xa0a83a 0xa082a7 0xa0835c 0xb19666 0xb1e8cc 0xae7c3f 0xae15ed 0xae46e7 0x747ed3 0x7490bd 0x74a9f3 0x665950 0x6571f1 
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0xade6e1]
 /usr/bin/mongod(_ZN5mongo10abruptQuitEi+0x399) [0x5582d9]
 /lib/x86_64-linux-gnu/libc.so.6(+0x364c0) [0x7f60e02cb4c0]
 /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35) [0x7f60e02cb445]
 /lib/x86_64-linux-gnu/libc.so.6(abort+0x17b) [0x7f60e02cebab]
 /usr/bin/mongod(_ZN5mongo10mongoAbortEPKc+0x47) [0xb503f7]
 /usr/bin/mongod() [0xa0a61a]
 /usr/bin/mongod(_ZN5mongo3dur11DurableImpl9commitNowEv+0x1a) [0xa0a83a]
 /usr/bin/mongod(_ZN5mongo3dur11DurableImpl16_aCommitIsNeededEv+0x157) [0xa082a7]
 /usr/bin/mongod(_ZN5mongo3dur11DurableImpl14commitIfNeededEb+0x4c) [0xa0835c]
 /usr/bin/mongod(_ZN5mongo12BtreeBuilderINS_12BtreeData_V1EE24mayCommitProgressDurablyEv+0x16) [0xb19666]
 /usr/bin/mongod(_ZN5mongo12BtreeBuilderINS_12BtreeData_V1EE6addKeyERNS_7BSONObjENS_7DiskLocE+0x30c) [0xb1e8cc]
 /usr/bin/mongod(_ZN5mongo24buildBottomUpPhases2And3INS_12BtreeData_V1EEEvbRNS_12IndexDetailsERNS_21BSONObjExternalSorterEbRSt3setINS_7DiskLocESt4lessIS7_ESaIS7_EEPNS_5CurOpEPNS_12SortPhaseOneERNS_19ProgressMeterHolderERNS_5TimerE+0x11f) [0xae7c3f]
 /usr/bin/mongod(_ZN5mongo14fastBuildIndexEPKcPNS_16NamespaceDetailsERNS_12IndexDetailsEi+0xc9d) [0xae15ed]
 /usr/bin/mongod(_ZN5mongo12buildAnIndexESsPNS_16NamespaceDetailsERNS_12IndexDetailsEib+0x217) [0xae46e7]
 /usr/bin/mongod(_ZN5mongo16insert_makeIndexEPNS_16NamespaceDetailsERKSsRKNS_7DiskLocE+0x1f3) [0x747ed3]
 /usr/bin/mongod(_ZN5mongo11DataFileMgr6insertEPKcPKvibbPb+0x93d) [0x7490bd]
 /usr/bin/mongod(_ZN5mongo11DataFileMgr16insertWithObjModEPKcRNS_7BSONObjEb+0x43) [0x74a9f3]
 /usr/bin/mongod(_ZN5mongo10CmdReIndex3runERKSsRNS_7BSONObjEiRSsRNS_14BSONObjBuilderEb+0xf90) [0x665950]
 /usr/bin/mongod(_ZN5mongo12_execCommandEPNS_7CommandERKSsRNS_7BSONObjEiRNS_14BSONObjBuilderEb+0x51) [0x6571f1]



***** SERVER RESTARTED *****


Fri Aug 31 02:35:15 [initandlisten] MongoDB starting : pid=18199 port=27017 dbpath=/media/newdrive/data 64-bit host=ip-10-138-30-205
Fri Aug 31 02:35:15 [initandlisten] db version v2.2.0, pdfile version 4.5
Fri Aug 31 02:35:15 [initandlisten] git version: f5e83eae9cfbec7fb7a071321928f00d1b0c5207
Fri Aug 31 02:35:15 [initandlisten] build info: Linux ip-10-2-29-40 2.6.21.7-2.ec2.v1.2.fc8xen #1 SMP Fri Nov 20 17:48:28 EST 2009 x86_64 BOOST_LIB_VERSION=1_49
Fri Aug 31 02:35:15 [initandlisten] options: { config: "/etc/mongodb.conf", dbpath: "/media/newdrive/data", logappend: "true", logpath: "/var/log/mongodb/mongodb.log" }
Fri Aug 31 02:35:15 [initandlisten] journal dir=/media/newdrive/data/journal
Fri Aug 31 02:35:15 [initandlisten] recover begin
Fri Aug 31 02:35:15 [initandlisten] recover lsn: 2093073
Fri Aug 31 02:35:15 [initandlisten] recover /media/newdrive/data/journal/j._3
Fri Aug 31 02:35:20 [initandlisten] recover cleaning up
Fri Aug 31 02:35:20 [initandlisten] removeJournalFiles
Fri Aug 31 02:35:20 [initandlisten] recover done
Fri Aug 31 02:35:20 [initandlisten] preallocating a journal file /media/newdrive/data/journal/prealloc.0
Fri Aug 31 02:35:23 [initandlisten]             157286400/1073741824    14%
Fri Aug 31 02:35:26 [initandlisten]             262144000/1073741824    24%
Fri Aug 31 02:35:29 [initandlisten]             367001600/1073741824    34%
Fri Aug 31 02:35:32 [initandlisten]             471859200/1073741824    43%
Fri Aug 31 02:35:35 [initandlisten]             576716800/1073741824    53%
Fri Aug 31 02:35:38 [initandlisten]             681574400/1073741824    63%
Fri Aug 31 02:35:41 [initandlisten]             786432000/1073741824    73%
Fri Aug 31 02:35:44 [initandlisten]             901775360/1073741824    83%
Fri Aug 31 02:35:47 [initandlisten]             1017118720/1073741824   94%
Fri Aug 31 02:35:51 [initandlisten] indexBuildInProgress was 1 for isikota.tablebusiness, indicating an abnormal db shutdown
Fri Aug 31 02:35:51 [initandlisten] waiting for connections on port 27017
Fri Aug 31 02:35:51 [websvr] admin web console waiting for connections on port 28017
Fri Aug 31 02:36:21 [initandlisten] connection accepted from 127.0.0.1:35505 #1 (1 connection now open)
Fri Aug 31 02:40:08 [conn1] end connection 127.0.0.1:35505 (0 connections now open)
mongodb ubuntu
Martin Hope
user4951
Asked: 2012-08-24 21:10:41 +0800 CST
  • 1

我的电脑上有一个mongodb数据库数据。我想将其上传到 amazon.com 服务器。

瓶颈是互联网连接。

一种方法是压缩文件，然后将其上传到服务器。但是如果在那之后我想将服务器数据与当前数据同步怎么办？

另一种是将服务器中的mongo设置为我计算机中数据的从属。但是，我的电脑是通过路由器连接到互联网的家用电脑。那么amazon服务器怎么知道我电脑的IP呢？IP 是 fe80::6df8:c5f9:bdb5:b9e2%15

好像是IPv6 IP。我不知道IP4的IP。

那么如何将我电脑中的一个服务器变成master呢？如何设置路由器传递连接？

然后是mongodump。快吗？

你通常使用哪一种？

mongodb
Martin Hope
user4951
Asked: 2012-08-24 20:59:45 +0800 CST
  • 3

我将一个集合复制到另一个集合。数据为 14GB。我删除了复制的集合。然而目录的大小仍然是 14 GB。

我选择了修复，但似乎要花很长时间。实际上插入全部数据只用了 20 分钟，修复到现在还在运行。

它只是压缩数据。

如果我不修复数据库会怎样？文件最终会被压缩吗？

mongodb
Martin Hope
user4951
Asked: 2012-08-21 23:11:44 +0800 CST
  • 7

MongoDB 与此查询的等价物是什么：

SELECT 111151.29341326 * SQRT( pow(-6.186753-`Latitude`, 2) 
                               + pow(106.772835-`Longitude`, 2)
                                 * cos(-6.186753*0.017453292519943)
                                 * cos(`Latitude`*0.017453292519943)
                             )
       as distance
from tablename ;

这使用haversine 公式来计算到固定点的大圆距离。我们想从一个位置获得最近的 20 个点，然后显示距离。

mysql mongodb
Martin Hope
user4951
Asked: 2012-08-17 02:44:45 +0800 CST
  • 1

在 phpmyadmin 中

假设我浏览了一张桌子，或者做任何事情。

我将被告知我应该运行以执行查询的实际 sql 命令。

假设我想做一个查询，然后我想做其他类似的事情。我可以运行一些查询。复制 SQL 命令，修改它，然后继续。这显然比从头开始编写查询更不容易出错，看到它不起作用，并且不知道出了什么问题。

在rockmongo我没有那个功能。

假设我想搜索某个记录。然后我想用额外的搜索来修改搜索。我不能那样做。

那么还有哪些其他软件可以访问具有该功能的 mongodb 呢？

另一个相关功能是在 phpmyadmin 中运行我希望的任何 sql 命令的功能。

我在 mongodb 中没有该功能。

mongodb rockmongo
Martin Hope
user4951
Asked: 2012-08-15 18:38:12 +0800 CST
  • 10

我的 mongodb 服务器有一个名为 villageContents 的数据库

它有一个名为 tablebusiness 的集合

如果我运行 mongo 我看到了

MongoDB shell version: 2.0.7
connecting to: test
>

我想知道什么是“测试”。那里没有名为 test 的数据库。

我试图执行

> villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})
Wed Aug 15 09:28:28 ReferenceError:is not defined (shell):1
>

我试图执行

> test.villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})
Wed Aug 15 09:29:13 ReferenceError: test is not defined (shell):1
>

我做错了什么？

然后我做了

db.villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})

什么都没有出现。甚至没有添加索引。

那么有什么问题呢？

mongodb
Martin Hope
user4951
Asked: 2012-08-09 07:26:30 +0800 CST
  • 1

我试图在我的机器上重新安装 mysql，但它一直失败。每次失败时，它都会执行此锁定，并且锁定和未来的努力将再次失败。

最后我决定做真正的干净安装

  • 删除所有内容
  • 重新安装

该过程在此处列出：http: //stuffthatspins.com/2011/01/08/ubuntu-10-x-completely-remove-and-clean-mysql-installation/

我不想丢失已经存在的数据。是的，它已备份，但我有点喜欢那里。

那么，我可以只 mv /etc/mysql 的内容，然后在重新安装所有内容后将其复制回来吗？

我的程序员说它不起作用

如果不是，我还应该知道什么？

我有点忘记mysql在哪里存储数据。我猜这是 ubuntu 的默认设置。

更新：我在使用可用计算机的一些本地数据库上进行了尝试。Myisam 表被转移。但是 innodb 表没有。

此外， http://www.chriscalender.com/? tag=innodb-error-tablespace-id -in-file 表明仅通过复制文件不会轻易转移 innodb。我错过了什么吗？

另一个更新：答案是肯定的，我们可以。我们做到了：D

更新伙计们。

简短的回答是肯定的，我们可以。我的程序员刚刚做到了。关键是复制整个文件并实现innodb将数据库存储在一个大文件中。不知道为什么。

另一个答案更好，所以我选择那个（我认为选择我自己的答案不是客观的）。我只是想分享它的工作原理。

mysql backup
Martin Hope
user4951
Asked: 2012-08-07 21:33:15 +0800 CST
  • 3

假设我们在 mysql 表中有一堆列。

经纬度

假设我们制作了一个索引（纬度和经度）

那么，索引将首先按纬度排序，如果纬度相同，它将按经度排序。

应该会出现一些可疑的东西。纬度很少（如果有的话）完全相同。

假设我们想要找到 Lat、Long 都“在一个盒子里”的所有点，这些索引在地球上到底有多大用处。

注意：我试图了解索引是如何工作的。我并不是想势利地说复合索引很愚蠢。不必是完整的答案。任何指向都可以。

更重要的是，我怀疑一个查询只会使用 1 个索引，而查询使用 multilpe 索引的唯一方法是创建复合索引。

mysql index
Martin Hope
user4951
Asked: 2012-06-23 01:28:38 +0800 CST
  • 0

我想查找某个点附近包含以 sea 开头的词的企业

lat=-6.185068&long=106.773121&keyword=sea&distance=5000&startfrom=0&howmany=20&prominent=5

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  TB.Latitude >-6.2317830813328 AND TB.Latitude < -6.1382169186672
   AND TB.Longitude >106.72621691867 AND TB.Longitude <106.81978308133
  AND MATCH (TA.FullTextSearch) AGAINST ('sea*' IN BOOLEAN MODE)
AND TA.Prominent >5
ORDER BY
  Distance
LIMIT
  0, 20

我得到了这些结果：

rasane__-6.18_106.77    -6.1826789706189    106.774375525435    299.434343596808
seafood-bangka---jakarta__-6.18_106.77  -6.18410749805068   106.767679452896    596.253784623613
seafood-sari-laut__-6.19_106.78     -6.18661497692636   106.7816047167  967.65389600759
rasane-seafood-restaurant__-6.18_106.76     -6.18244550827744   106.763621456218    1074.56073316741
pecel-lele-lela__-6.19_106.78   -6.18836286783996   106.782370851856    1100.91660371997
bintang-laut-seafood__-6.18_106.77  -6.17782494896301   106.766016483307    1109.75958732106
d'cost-itc-permata-hijau__-6.19_106.77  -6.19494204046746   106.770504692048    1138.95344788546
masterpiece---car-leather-seat-and-interior__-6.18...   -6.18373417378944   106.783719062805    1192.82955595065
seafood-makasar__-6.18_106.78   -6.17684    106.780666  1241.07275264282
hongkong-jumbo-seafood-restaurant__-6.20_106.77     -6.19668602280699   106.771334150698    1311.89593536338
plg-(pecel-lele-greenville)__-6.17_106.78   -6.17354759635866   106.7768121112  1340.84148631128
makassar__-6.18_106.78  -6.17990504916375   106.784087464957    1349.76048368113
makassar-seafood__-6.20_106.77  -6.19642790400783   106.768355369568    1370.00078243887
pelangi-seafood__-6.17_106.78   -6.1733876537374    106.777818202972    1396.23099579268
sea-food-sari-laut-kemanggisan__-6.19_106.79    -6.188395   106.7853    1410.61411729396
seafood-toni-k__-6.19_106.79    -6.188435   106.785291  1410.85254243932
seafood-sari-laut-tony-koeswoyo__-6.19_106.79   -6.188445   106.785417  1424.55989808634
makassar-seafood-restaurant__-6.18_106.78   -6.177669   106.783744  1439.9871117091
sea-food-38-kebon-jeruk__-6.20_106.77   -6.19710303086272   106.767730987066    1465.8643124913
seafood-38-(lap.-bola)__-6.20_106.77    -6.197078   106.767655  1466.67274960557

现在我基于一些略有不同的东西运行类似的查询 lat=-6.185564&long=106.775518&keyword=Sea&distance=5000&startfrom=0&howmany=20&prominent=5 注意经纬度只有一点点不同

查询没有太大变化

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.186-TB.Latitude,2)+pow(106.776-TB.Longitude,2)*0.98838855059148) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  TB.Latitude >-6.2327830813328 AND TB.Latitude < -6.1392169186672
   AND TB.Longitude >106.72921691867 AND TB.Longitude <106.82278308133
  AND MATCH (TA.FullTextSearch) AGAINST ('Sea*' IN BOOLEAN MODE)
AND TA.Prominent >5
ORDER BY
  Distance
LIMIT
  0, 20

这个返回空

我并排放置了 2 个查询。显然，许多结果都在矩形内。

怎么来的？

任何人都可以在其中发现一些明显的错误吗？也许我在 SQL 命令中做错了什么

full-text-search mysql-5.5
Martin Hope
user4951
Asked: 2012-06-22 05:56:42 +0800 CST
  • 6

看看这个查询

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
   AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

这基本上是搜索所有 TA.LatLong 在“MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)”框中的公司，之后该框必须包含 kucing

这将返回 22 行。

现在将其与此查询进行比较

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
ORDER BY
  Distance
LIMIT
  0, 20

这是相同的查询，但限制较少。现在我只想检查业务是否在框中。这就对了。是的，那些盒子里有企业。其中大部分不包含“kucing*”字样

应该有更多的查询。然而，更少。第二个查询返回 null。LESS 限制性查询返回 LESS 结果。

罗兰多一口气回答得很好。

带有 MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE) 的那个是可以正常工作的那个。

我在问为什么没有的那个是有问题的。罗兰多似乎试图纠正正确的。

注意：（我想知道这是否应该是另一个问题）。听别人说后我试过：删除不同的

SELECT 
  TB.ID,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1939967464102 106.76400325359,-6.1760032535898 106.78199674641)'
            ),
            TA.Latlong) 
AND TA.Prominent >15
ORDER BY
  Distance
LIMIT
  0, 20

返回 0 行

然后我尝试删除订单和限制

SELECT 
  TB.ID,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1939967464102 106.76400325359,-6.1760032535898 106.78199674641)'
            ),
            TA.Latlong) 
AND TA.Prominent >15

这也导致 0 行最后我使用原始查询并确保我只删除

    AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)

将结果从一些结果变成没有结果

最后我使查询变得非常简单

SELECT BusinessID, Prominent
FROM
    tableauxiliary as TA
WHERE
  MBRContains(GeomFromText ('MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)'),TA.Latlong)

这就是有史以来最基本的查询。

仍然没有返回结果

现在这是一个奇迹。我添加了 2 个字符，它再次起作用。我添加的字符是=1

SELECT BusinessID
FROM tableauxiliary AS TA
WHERE MBRContains( GeomFromText( 'MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)' ) , TA.Latlong )
 =1

我得到了结果

我想是时候报告错误了。

mysql mysql-5.5
Martin Hope
user4951
Asked: 2012-06-22 02:44:14 +0800 CST
  • 4

对于查找最近的邻居之类的事情，我可以理解 R=Tree 可以胜过 B-Tree。R-Tree可以踢出更明显的假点。

然而，对于矩形中的一个点的简单检查，它是有效的

分钟

如果 x 和 y 被 b 树索引，它们也可以踢出很多假点。

所以我用空间索引做了一个实验

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
   AND 
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

这非常快。.24 秒

然后我做

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA
WHERE
  -6.2317830813328 < TB.Latitude
AND
  TB.Latitude < -6.1382169186672
AND
  106.72621691867 < TB.Longitude
AND
  TB.Longitude <106.81978308133
AND 
   MATCH (TA.FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

这很慢。

我想知道为什么。这没有意义。

mysql spatial
Martin Hope
user4951
Asked: 2012-06-15 23:29:59 +0800 CST
  • 2

在 MySQL 中？

假设我有一个本地数据库。现在我想让远程数据库模仿本地数据库。

我能想到的一种方法是简单地转储数据库，上传并让一切正常进行。

嗯，数据库很大。

另一个我能想到的是直接连接到数据库并用专用的 vb.net 程序逐行填充。

有时我只想在同一台服务器上创建一个备份数据库。

亲是怎么做到的？

mysql
Martin Hope
user4951
Asked: 2012-06-14 23:16:53 +0800 CST
  • 1

我有一个包含 160 万行的表。

然后我在该表上添加一个字段。

我正在使用 hostgator 作为托管。你知道，他们有 16 核 CPU。

结果： 1. phpmyadmin 没有工作 10 分钟 2. 数据库重新联机后，我想添加的字段未添加。3. 幸运的是，我没有接到来自 hostgator 的投诉电话。

我不在乎它很慢。我知道它很慢。这是 160 万行。我只想做一次。

这是因为我使用了innodb引擎吗？

命令很简单：

Alter table tablebusiness add column `Prominent` int(10) NOT NULL

我收到此错误：

#2013 - 查询期间与 MySQL 服务器失去连接

我不需要快速运行此操作

我需要它运行一次。

我想我可以将表复制到其他地方，然后将旧表切换到新表，但 innodb 有级联，我不知道该怎么做。

其他更新：Hostgator 很生气。不能怪他们。我一定使用了巨大的 CPU 能力。

在我看来，有一种修改表格的正确方法。也许先在维护模式下关闭数据库之类的。也许先创建一个表的副本。

mysql
Martin Hope
user4951
Asked: 2012-06-12 20:35:11 +0800 CST
  • 2

假设我想要 20 家离某个位置最近的企业。

Innodb 不支持。那么有什么作用呢？

是否可以在某些引擎中拥有一些表，而在不同引擎上拥有其他表？

mysql