Início / user-2613

user4951's questions

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

Percebi que $near e $nearSphere retornam dados completamente diferentes e fiquei desconfiado. Afinal, quando a latitude está próxima do equador, a distância da esfera não deve diferir muito da distância euclidiana.

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

Ele retorna 48 dados.

vou anexar mesmo assim

/* 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"]
}

Eu coloco os dados no excel e calculo a distância usando esta fórmula

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

Esse deve se aproximar bastante da distância esférica. Observe também que a latitude é -6, que não está longe do equador.

aqui está o resultado

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

Aqui está a captura de tela da minha planilha do Excelinsira a descrição da imagem aqui

Aqui está o Excel real

            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

Olhe novamente o termo na consulta

"$maxDistance" : 0.053980478460939611

Vai saber.

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

parece que ntoreturn significa número a retornar. ntoskip são números para pular. nscanned parece ser a soma dos 2.

O que é reslen?

Pesquiso no google sem resposta. Qual site explica o que é reslen?

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

Se sim, qual?

Como nós sabemos?

Eu tenho 3 índices em uma consulta. Na verdade, apenas os 2 primeiros devem ser úteis

{
   "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 
}

Este não é necessário:

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

Agora eu faço esta consulta

(a consulta é do PhP, então vou apenas colar o que vejo no mongodb log e no 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

Agora, copiar { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } }para o comando mongovue find me dá o comando real do banco de dados

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

O que é bom o suficiente (exceto que o limite muda um pouco). Fazendo explicar em mongo.exe eu tenho

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" : {

        }
}

Agora, com base nisso, qual índice é usado? O mongodb usa algum índice? Quaisquer dicas sobre como melhorar esta consulta também serão apreciadas.

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

Já mudei o dono do diretório que quero usar

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#

Eu já reiniciei o 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#

meu mongodb.conf já foi alterado

# 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

O que mais eu deveria ter feito? Estou usando amazon ec2

Atualização: já sei qual é o problema, mas aqui está o log de qualquer maneira por comentários. Obrigado pela resposta.

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

Eu tenho um banco de dados mongodb no meu computador. Eu quero fazer o upload para o servidor amazon.com.

O gargalo da garrafa é a conexão com a internet.

Uma maneira é compactar o arquivo e enviá-lo para o servidor. Mas e se depois disso eu quiser sincronizar os dados do servidor com os dados atuais?

Outra é definir o mongo no servidor como escravo dos dados no meu computador. No entanto, meu computador é um computador doméstico que se conecta à Internet via roteador. Então, como o servidor amazon conhece o IP do meu computador? O IP é fe80::6df8:c5f9:bdb5:b9e2%15

Parece que é o IP IPv6. Não conheço o IP IP4.

Então, como faço para transformar um servidor no meu computador em mestre? Como configuro o roteador para passar na conexão?

Depois, há o mongodump. É rápido?

Qual você costuma usar?

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

Eu copio uma coleção para outra coleção. Os dados são de 14 GB. Apaguei a coleção copiada. No entanto, o tamanho do diretório ainda é de 14 GB.

Eu escolhi consertar, mas parece demorar uma eternidade. Na verdade, inserir todos os dados levou apenas 20 minutos e o reparo ainda está em execução até agora.

É apenas compactar os dados.

O que acontece se eu não reparar o banco de dados? Os arquivos eventualmente serão compactados?

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

Qual é o equivalente do MongoDB para esta consulta:

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 ;

Isso usa a fórmula de Haversine para calcular a distância do grande círculo a um ponto fixo. Queremos chegar mais perto de 20 pontos de um local e, em seguida, exibir a distância.

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

No phpmyadmin

Digamos que eu navegue em uma mesa ou faça qualquer coisa.

Será informado o comando sql real que devo executar para executar a consulta.

Digamos que eu queira fazer uma consulta e, em seguida, quero fazer algo semelhante. Eu posso apenas executar algumas consultas. Copie o comando SQL, modifique-o e siga em frente. Obviamente, isso é menos propenso a erros do que escrever consultas do zero, ver se não funciona e não saber o que está errado.

No rockmongo não tenho esse recurso.

Digamos que eu queira pesquisar determinado registro. Em seguida, desejo modificar a pesquisa com pesquisa adicional. Eu não posso fazer isso.

Então, qual outro software para acessar o mongodb que oferece esse recurso?

Outro recurso relacionado é um recurso para executar qualquer comando sql que eu desejar no phpmyadmin.

Eu não tenho esse recurso no mongodb.

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

Meu servidor mongodb tem um banco de dados chamado villageContents

Tem uma coleção chamada tablebusiness

Se eu correr mongo eu vi

MongoDB shell version: 2.0.7
connecting to: test
>

Eu me pergunto o que é "teste". Não há nenhum banco de dados chamado teste lá.

tentei executar

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

tentei executar

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

O que eu fiz errado?

Então eu fiz

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

Nada aparece. Os índices nem são adicionados.

Então, oque há de errado?

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

Tentei reinstalar o mysql na minha máquina, mas continua falhando. Toda vez que ele falha, ele faz esse travamento e aquele travamento e esforço futuro falharia novamente.

Finalmente, decidi fazer uma instalação verdadeiramente limpa

  • Excluir tudo
  • Reinstalar

O procedimento está listado aqui: http://stuffthatspins.com/2011/01/08/ubuntu-10-x-completely-remove-and-clean-mysql-installation/

Não quero perder os dados que já estão lá. Sim, é feito backup, mas eu meio que gosto dele lá.

Então, posso apenas mv conteúdo de /etc/mysql e copiá-lo de volta depois de reinstalar tudo?

Meu programador disse que não iria funcionar

Se não, o que mais devo saber?

Eu meio que esqueço onde o mysql armazena os dados. É o padrão para o Ubuntu, eu acho.

Atualização: tentei isso em alguns bancos de dados locais usando computadores disponíveis. A mesa Myisam é transferida. No entanto, as tabelas innodb não.

Também http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file mostra que innodb não será transferido tão facilmente apenas copiando arquivos. Estou esquecendo de algo?

Outra atualização: a resposta é sim, podemos. Conseguimos :D

Atualiza galera.

A resposta curta é sim, podemos. Meu programador acabou de fazer isso. A chave é copiar o arquivo inteiro e perceber que o innodb armazena o banco de dados em um arquivo grande. Não sei por quê.

A outra resposta é melhor, então eu a escolho (não acho que seja objetivo escolher minha própria resposta). Eu só quero compartilhar que funciona.

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

Digamos que temos várias colunas na tabela mysql.

Latitude longitude

Digamos que façamos um índice (latitude e longitude)

Bem, o índice classificará primeiro por Latitude e SE a Latitude for a mesma, ele classificará por Longitude.

Algo suspeito deve aparecer. A latitude raramente, ou nunca, é exatamente a mesma.

Digamos que queremos encontrar todos os pontos cujo Lat,Long está "em uma caixa" Como na Terra esses índices podem ser úteis.

Observação: estou tentando entender como funciona a indexação. Não estou tentando dizer esnobemente que o índice composto é estúpido. Não precisa ser uma resposta completa. Qualquer apontamento é bom.

Mais importante, suspeito que uma consulta usará apenas 1 índice e a única maneira de uma consulta usar o índice multilpe é criar um índice composto.

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

Quero encontrar empresas perto de um ponto que contenha uma palavra que comece com mar

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

Eu tenho esses resultados:

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

Agora eu executo a consulta semelhante com base em algo ligeiramente diferente lat=-6.185564&long=106.775518&keyword=Sea&distance=5000&startfrom=0&howmany=20&prominent=5 Observe que lat e long diferem apenas um pouco

A consulta não muda muito

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

Este retorna vazio

Coloquei as 2 consultas lado a lado. Obviamente muitos dos resultados estão dentro do retângulo.

Por quê?

Alguém pode identificar alguns erros óbvios neles? Talvez eu tenha feito algo errado no comando SQL

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

Veja esta consulta

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

isso é basicamente pesquisar todos os negócios cujo TA.LatLong está na caixa 'MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)' e depois disso a caixa deve conter kucing

Isso retorna 22 linhas.

Agora, comparado a esta consulta

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

É a mesma consulta, mas MENOS restritiva. Agora só quero verificar se o negócio está na caixa ou não. É isso. Sim, existem empresas nessas caixas. A maioria dos quais não contém a palavra "kucing*"

Deve haver mais perguntas. No entanto, há menos. A segunda consulta retorna null. A consulta menos restritiva retorna o resultado MENOS.

A resposta de Rolando é boa com uma captura.

Aquele com MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE) é aquele que funciona corretamente.

Estou perguntando por que aquele sem é problemático. Rolando parece tentar corrigir os corretos.

Nota: (Eu me pergunto se isso deveria ser outra pergunta). Depois de ouvir outros, tentei: Depois de remover

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

retornando 0 linhas

Então eu tentei remover a ordem por e limitar

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

O que também resulta em 0 linhas, finalmente, eu uso a consulta original e certifico-me de remover apenas

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

transformar resultados de alguns resultados em nenhum resultado

Finalmente eu faço a consulta realmente simples

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

Essa é a consulta mais básica de todos os tempos.

Ainda não retornou nenhum resultado

Ora aqui está um milagre. Eu adiciono 2 caracteres e funciona novamente. Os caracteres que adiciono são =1

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

eu tenho resultado

Acho que é hora de relatar bugs.

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

Para coisas como encontrar vizinhos mais próximos, posso entender que R=Tree pode superar B-Tree. O R-Tree pode lançar pontos mais obviamente falsos.

No entanto, para verificação simples de um ponto em um retângulo, é efetivamente

min

Se x e y forem indexados pela árvore b, eles também podem gerar muitos pontos falsos.

Então eu fiz um experimento com índice espacial

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

Isso é muito rápido. 0,24 segundos

E então eu faço

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

Isso é muito lento.

Eu quero saber porque. Não faz sentido.

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

No MySQL?

Digamos que eu tenha um banco de dados local. Agora eu quero que o banco de dados remoto imite o banco de dados local.

Uma maneira que posso pensar é simplesmente despejar o banco de dados, fazer o upload e deixar as coisas funcionarem.

Bem, o banco de dados é grande.

Outro que posso pensar é conectar-se diretamente ao banco de dados e preencher a linha uma a uma com um programa vb.net dedicado.

Às vezes, eu só quero criar um banco de dados de backup no mesmo servidor.

Como o profissional faz isso?

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

Eu tenho uma tabela com 1,6 milhões de linhas.

Então eu adiciono um campo nessa tabela.

Estou usando hostgator como hospedagem. Você sabe, eles têm uma CPU de 16 núcleos.

Resultado: 1. O phpmyadmin não funciona por 10 minutos 2. Após o banco de dados voltar a ficar online, o campo que desejo adicionar NÃO foi adicionado. 3. Felizmente, não recebo uma ligação do hostgator reclamando.

Eu não me importo, é lento. Eu sei que é lento. São 1,6 milhão de linhas. Eu só quero fazer isso uma vez.

Isso é porque eu uso o mecanismo innodb?

O comando é simples:

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

Eu recebi este erro:

#2013 - Conexão perdida com o servidor MySQL durante a consulta

Não preciso que esta operação seja executada rapidamente

Eu preciso que ele seja executado uma vez.

Suponho que posso copiar a tabela para outro lugar e, em seguida, mudar a tabela antiga para a nova tabela, mas o innodb está em cascata e não sei como fazer isso.

Outra atualização: Hostgator está chateado. Não posso culpá-los. Devo ter usado uma grande potência da CPU.

Parece-me que existe uma maneira adequada de modificar uma tabela. Talvez desligar o banco de dados primeiro no modo de manutenção ou algo assim. Talvez criando uma cópia da tabela primeiro.

mysql