SQL baze podataka - Sponzor: Baze-Podataka.net MySQL, MSSQL, Oracle, Access, ODBC. Ako imate problem brže i preciznije ćete dobiti odgovor ako priložite strukturu tabela ili skript koji kreira tabele i puni ih test podacima umesto što to problem opisujete samo rečima. Sponzor: Baze-Podataka.net - Blog o bazama podataka |
|
Alati teme | Način prikaza |
18. 06. 2009. | #61 |
old school
Expert
Datum učlanjenja: 29.04.2006
Poruke: 464
Hvala: 39
48 "Hvala" u 28 poruka
|
btw, sada sam skontao da je ovaj prvi explain koji sam ja radio da bih shvatio sta fali upitu :S
|
19. 06. 2009. | #62 | |
novi član
Datum učlanjenja: 03.06.2009
Poruke: 5
Hvala: 0
0 "Hvala" u 0 poruka
|
Citat:
SELECT u.id, u.membership, FLOOR((6371.0 * acos(sin(40.7519*0.01745329252) * sin(u.latitude*0.01745329252) + cos(40.7519*0.01745329252) * cos(u.latitude*0.01745329252) * cos((-73.9954*0.01745329252)-(u.longitude*0.01745329252)))) / 1.609344) as distance FROM users u INNER JOIN users_search us ON u.id = us.user_id WHERE u.id != 1 AND u.gender = 2 AND u.seeking = 3 AND u.approved = 1 AND u.deleted = 0 AND (u.latitude > -2.7438837837838 AND u.latitude < 84.247683783784) AND (u.longitude > -131.24672706316 AND u.longitude < -16.744072936839) AND u.dob > '1909-06-19' AND u.dob < '1991-06-19' AND u.id NOT IN (SELECT user_id FROM tier_2_users where u.id= tier_2_users.user_id) AND u.id NOT IN (SELECT blocked_user_id FROM blocked WHERE user_id = '1') AND u.id NOT IN (SELECT user_id FROM personal_definitions_rejected) AND (us.z_39 LIKE '%|386|%' OR us.z_39 LIKE '%|387|%' OR us.z_39 LIKE '%|388|%' OR us.z_39 LIKE '%|389|%' OR us.z_39 LIKE '%|390|%' OR us.z_39 LIKE '%|391|%' OR us.z_39 LIKE '%|392|%' OR us.z_39 LIKE '%|393|%' OR us.z_39 LIKE '%|394|%' OR us.z_39 LIKE '%|395|%' OR us.z_39 LIKE '%|396|%' OR us.z_39 LIKE '%|397|%' OR us.z_39 LIKE '%|398|%' OR us.z_39 LIKE '%|399|%' OR us.z_39 LIKE '%|400|%' OR us.z_39 LIKE '%|401|%' OR us.z_39 LIKE '%|486|%' OR us.z_39 LIKE '%|487|%' OR us.z_39 LIKE '%|664|%') AND (us.z_9 = 137 OR us.z_9 = 138 OR us.z_9 = 139 OR us.z_9 = 140 OR us.z_9 = 141 OR us.z_9 = 142 OR us.z_9 = 433 OR us.z_9 = 434 OR us.z_9 = 481 OR us.z_9 = 482 OR us.z_9 = 483 OR us.z_9 = 490) AND (us.z_12 = 214 OR us.z_12 = 215 OR us.z_12 = 216 OR us.z_12 = 217 OR us.z_12 = 218 OR us.z_12 = 220) AND (us.z_13 LIKE '%|221|%' OR us.z_13 LIKE '%|222|%' OR us.z_13 LIKE '%|223|%' OR us.z_13 LIKE '%|224|%' OR us.z_13 LIKE '%|225|%' OR us.z_13 LIKE '%|226|%' OR us.z_13 LIKE '%|227|%' OR us.z_13 LIKE '%|429|%' OR us.z_13 LIKE '%|435|%' OR us.z_13 LIKE '%|495|%') AND (us.z_14 = 228 OR us.z_14 = 229 OR us.z_14 = 230 OR us.z_14 = 231 OR us.z_14 = 488 OR us.z_14 = 489 OR us.z_14 = 646) AND (us.z_15 = 232 OR us.z_15 = 233 OR us.z_15 = 234 OR us.z_15 = 645) AND (us.z_16 = 238 OR us.z_16 = 239 OR us.z_16 = 240) AND (us.z_50 = 496 OR us.z_50 = 497 OR us.z_50 = 498 OR us.z_50 = 499 OR us.z_50 = 500 OR us.z_50 = 501 OR us.z_50 = 502 OR us.z_50 = 503 OR us.z_50 = 504 OR us.z_50 = 505 OR us.z_50 = 506 OR us.z_50 = 507 OR us.z_50 = 508) AND (us.z_51 LIKE '%|509|%' OR us.z_51 LIKE '%|510|%' OR us.z_51 LIKE '%|511|%' OR us.z_51 LIKE '%|512|%' OR us.z_51 LIKE '%|513|%' OR us.z_51 LIKE '%|514|%' OR us.z_51 LIKE '%|515|%' OR us.z_51 LIKE '%|516|%' OR us.z_51 LIKE '%|517|%' OR us.z_51 LIKE '%|518|%' OR us.z_51 LIKE '%|519|%' OR us.z_51 LIKE '%|647|%') AND (us.z_60 = 682 OR us.z_60 = 683) AND (us.z_17 = 242 OR us.z_17 = 243 OR us.z_17 = 244 OR us.z_17 = 245 OR us.z_17 = 246 OR us.z_17 = 436) AND (us.z_18 = 248 OR us.z_18 = 249 OR us.z_18 = 250 OR us.z_18 = 251 OR us.z_18 = 252 OR us.z_18 = 253 OR us.z_18 = 254 OR us.z_18 = 437) AND (us.z_19 = 256 OR us.z_19 = 257 OR us.z_19 = 258 OR us.z_19 = 259 OR us.z_19 = 260 OR us.z_19 = 261 OR us.z_19 = 262 OR us.z_19 = 263 OR us.z_19 = 264 OR us.z_19 = 265 OR us.z_19 = 266 OR us.z_19 = 267 OR us.z_19 = 268 OR us.z_19 = 269 OR us.z_19 = 270 OR us.z_19 = 271 OR us.z_19 = 272 OR us.z_19 = 273 OR us.z_19 = 274 OR us.z_19 = 553 OR us.z_19 = 554 OR us.z_19 = 555) AND (us.z_21 = 276 OR us.z_21 = 277 OR us.z_21 = 278 OR us.z_21 = 279 OR us.z_21 = 280 OR us.z_21 = 281) AND (us.z_22 = 285 OR us.z_22 = 286 OR us.z_22 = 287 OR us.z_22 = 288 OR us.z_22 = 492 OR us.z_22 = 493 OR us.z_22 = 494) AND (us.z_23 = 290 OR us.z_23 = 291 OR us.z_23 = 292 OR us.z_23 = 491 OR us.z_23 = 663) AND (us.z_24 LIKE '%|294|%' OR us.z_24 LIKE '%|295|%' OR us.z_24 LIKE '%|296|%' OR us.z_24 LIKE '%|297|%' OR us.z_24 LIKE '%|298|%') AND (us.z_25 = 299 OR us.z_25 = 300 OR us.z_25 = 301 OR us.z_25 = 302 OR us.z_25 = 454 OR us.z_25 = 455) AND (us.z_27 LIKE '%|312|%' OR us.z_27 LIKE '%|313|%' OR us.z_27 LIKE '%|314|%' OR us.z_27 LIKE '%|315|%' OR us.z_27 LIKE '%|316|%' OR us.z_27 LIKE '%|317|%' OR us.z_27 LIKE '%|318|%' OR us.z_27 LIKE '%|319|%' OR us.z_27 LIKE '%|320|%' OR us.z_27 LIKE '%|344|%' OR us.z_27 LIKE '%|345|%' OR us.z_27 LIKE '%|346|%' OR us.z_27 LIKE '%|347|%') AND (us.z_29 LIKE '%|321|%' OR us.z_29 LIKE '%|322|%' OR us.z_29 LIKE '%|323|%' OR us.z_29 LIKE '%|324|%' OR us.z_29 LIKE '%|325|%' OR us.z_29 LIKE '%|326|%' OR us.z_29 LIKE '%|327|%' OR us.z_29 LIKE '%|328|%' OR us.z_29 LIKE '%|329|%' OR us.z_29 LIKE '%|330|%' OR us.z_29 LIKE '%|331|%' OR us.z_29 LIKE '%|332|%' OR us.z_29 LIKE '%|456|%') AND (us.z_33 LIKE '%|362|%' OR us.z_33 LIKE '%|363|%' OR us.z_33 LIKE '%|364|%' OR us.z_33 LIKE '%|365|%' OR us.z_33 LIKE '%|366|%' OR us.z_33 LIKE '%|367|%' OR us.z_33 LIKE '%|368|%' OR us.z_33 LIKE '%|369|%' OR us.z_33 LIKE '%|370|%' OR us.z_33 LIKE '%|371|%' OR us.z_33 LIKE '%|648|%' OR us.z_33 LIKE '%|649|%' OR us.z_33 LIKE '%|650|%' OR us.z_33 LIKE '%|651|%' OR us.z_33 LIKE '%|652|%' OR us.z_33 LIKE '%|653|%' OR us.z_33 LIKE '%|654|%' OR us.z_33 LIKE '%|655|%' OR us.z_33 LIKE '%|656|%' OR us.z_33 LIKE '%|657|%' OR us.z_33 LIKE '%|658|%' OR us.z_33 LIKE '%|661|%') AND (us.z_58 LIKE '%|605|%' OR us.z_58 LIKE '%|606|%' OR us.z_58 LIKE '%|607|%' OR us.z_58 LIKE '%|608|%' OR us.z_58 LIKE '%|609|%' OR us.z_58 LIKE '%|610|%' OR us.z_58 LIKE '%|611|%' OR us.z_58 LIKE '%|612|%' OR us.z_58 LIKE '%|613|%' OR us.z_58 LIKE '%|614|%' OR us.z_58 LIKE '%|615|%' OR us.z_58 LIKE '%|616|%' OR us.z_58 LIKE '%|617|%' OR us.z_58 LIKE '%|618|%' OR us.z_58 LIKE '%|619|%' OR us.z_58 LIKE '%|620|%' OR us.z_58 LIKE '%|621|%' OR us.z_58 LIKE '%|622|%' OR us.z_58 LIKE '%|623|%' OR us.z_58 LIKE '%|624|%' OR us.z_58 LIKE '%|625|%' OR us.z_58 LIKE '%|627|%' OR us.z_58 LIKE '%|628|%' OR us.z_58 LIKE '%|630|%' OR us.z_58 LIKE '%|631|%' OR us.z_58 LIKE '%|632|%' OR us.z_58 LIKE '%|633|%' OR us.z_58 LIKE '%|634|%' OR us.z_58 LIKE '%|635|%' OR us.z_58 LIKE '%|636|%' OR us.z_58 LIKE '%|637|%' OR us.z_58 LIKE '%|639|%' OR us.z_58 LIKE '%|640|%') AND (us.z_31 LIKE '%|333|%' OR us.z_31 LIKE '%|334|%' OR us.z_31 LIKE '%|335|%' OR us.z_31 LIKE '%|336|%' OR us.z_31 LIKE '%|337|%' OR us.z_31 LIKE '%|338|%' OR us.z_31 LIKE '%|339|%' OR us.z_31 LIKE '%|341|%' OR us.z_31 LIKE '%|342|%' OR us.z_31 LIKE '%|343|%' OR us.z_31 LIKE '%|447|%' OR us.z_31 LIKE '%|448|%' OR us.z_31 LIKE '%|659|%' OR us.z_31 LIKE '%|660|%') AND (us.z_32 LIKE '%|348|%' OR us.z_32 LIKE '%|350|%' OR us.z_32 LIKE '%|351|%' OR us.z_32 LIKE '%|352|%' OR us.z_32 LIKE '%|353|%' OR us.z_32 LIKE '%|354|%' OR us.z_32 LIKE '%|355|%' OR us.z_32 LIKE '%|356|%' OR us.z_32 LIKE '%|357|%' OR us.z_32 LIKE '%|358|%' OR us.z_32 LIKE '%|360|%' OR us.z_32 LIKE '%|361|%' OR us.z_32 LIKE '%|438|%' OR us.z_32 LIKE '%|439|%' OR us.z_32 LIKE '%|440|%' OR us.z_32 LIKE '%|441|%' OR us.z_32 LIKE '%|442|%' OR us.z_32 LIKE '%|443|%' OR us.z_32 LIKE '%|444|%' OR us.z_32 LIKE '%|445|%' OR us.z_32 LIKE '%|446|%' OR us.z_32 LIKE '%|595|%' OR us.z_32 LIKE '%|596|%' OR us.z_32 LIKE '%|597|%' OR us.z_32 LIKE '%|598|%' OR us.z_32 LIKE '%|599|%' OR us.z_32 LIKE '%|600|%' OR us.z_32 LIKE '%|702|%') AND (us.z_35 LIKE '%|372|%' OR us.z_35 LIKE '%|373|%' OR us.z_35 LIKE '%|374|%' OR us.z_35 LIKE '%|375|%' OR us.z_35 LIKE '%|449|%' OR us.z_35 LIKE '%|450|%' OR us.z_35 LIKE '%|567|%' OR us.z_35 LIKE '%|568|%' OR us.z_35 LIKE '%|569|%' OR us.z_35 LIKE '%|571|%' OR us.z_35 LIKE '%|572|%' OR us.z_35 LIKE '%|573|%' OR us.z_35 LIKE '%|584|%' OR us.z_35 LIKE '%|585|%' OR us.z_35 LIKE '%|586|%' OR us.z_35 LIKE '%|588|%' OR us.z_35 LIKE '%|589|%' OR us.z_35 LIKE '%|590|%') AND (us.z_38 LIKE '%|376|%' OR us.z_38 LIKE '%|377|%' OR us.z_38 LIKE '%|378|%' OR us.z_38 LIKE '%|379|%' OR us.z_38 LIKE '%|380|%' OR us.z_38 LIKE '%|381|%' OR us.z_38 LIKE '%|382|%' OR us.z_38 LIKE '%|383|%' OR us.z_38 LIKE '%|384|%' OR us.z_38 LIKE '%|385|%' OR us.z_38 LIKE '%|452|%') AND (us.z_40 = 402 OR us.z_40 = 403 OR us.z_40 = 404 OR us.z_40 = 405 OR us.z_40 = 406 OR us.z_40 = 407 OR us.z_40 = 408 OR us.z_40 = 409 OR us.z_40 = 410 OR us.z_40 = 411 OR us.z_40 = 556 OR us.z_40 = 559 OR us.z_40 = 692) AND (us.z_41 = 413 OR us.z_41 = 414 OR us.z_41 = 415 OR us.z_41 = 416 OR us.z_41 = 417 OR us.z_41 = 418 OR us.z_41 = 419) AND (us.z_42 = 421 OR us.z_42 = 422 OR us.z_42 = 423 OR us.z_42 = 424 OR us.z_42 = 425 OR us.z_42 = 426 OR us.z_42 = 427 OR us.z_42 = 453 OR us.z_42 = 641 OR us.z_42 = 643 OR us.z_42 = 644 OR us.z_42 = 662 OR us.z_42 = 665) AND (us.z_53 LIKE '%|538|%' OR us.z_53 LIKE '%|539|%' OR us.z_53 LIKE '%|540|%' OR us.z_53 LIKE '%|541|%' OR us.z_53 LIKE '%|542|%' OR us.z_53 LIKE '%|543|%' OR us.z_53 LIKE '%|544|%' OR us.z_53 LIKE '%|545|%' OR us.z_53 LIKE '%|546|%' OR us.z_53 LIKE '%|547|%' OR us.z_53 LIKE '%|548|%' OR us.z_53 LIKE '%|549|%' OR us.z_53 LIKE '%|550|%' OR us.z_53 LIKE '%|551|%' OR us.z_53 LIKE '%|560|%') AND (us.z_55 LIKE '%|561|%' OR us.z_55 LIKE '%|562|%' OR us.z_55 LIKE '%|563|%' OR us.z_55 LIKE '%|564|%' OR us.z_55 LIKE '%|565|%') ORDER BY distance ASC, u.membership DESC LIMIT 500 Ne znam ko ga je pravio sa onih 30-ak joina. Ne znam ko ga je pravio, mozda i boss himself, ne znam da li je dobio otkaz . Posle su dali meni da napravim to da radi sa 100+ opcija i to tako sljaka odavno. z_xx tabele sam denormalizovao, mozete zamisliti koliko je trebalo vremena za 50+ baza i 4+ miliona korisnika. Ako kazete opet da nisam normalan, bacicu se sa terase (doduse nisam visoko). Hajde sada, opalite (ali mooolim konstruktivno). |
|
19. 06. 2009. | #63 |
Goran Pilipović
Sir Write-a-Lot
|
Pa kako, bre, konstruktivno kada ne može živ čovek da provali šta je šta
Ovde očigledno treba drugačija struktura baze ako ovo baš mora da se izvršava.
__________________
Goran Pilipović a.k.a. Ugly Fingers Bradley f.k.a. bluesman I don't always know what I'm talking about but I know I'm right! |
19. 06. 2009. | #64 | |
novi član
Datum učlanjenja: 03.06.2009
Poruke: 5
Hvala: 0
0 "Hvala" u 0 poruka
|
Citat:
Nisam siguran da smem cak i preko private. |
|
19. 06. 2009. | #65 |
VD IT Direktora
Invented the damn thing
Datum učlanjenja: 08.06.2005
Lokacija: Beograd
Poruke: 2.118
Hvala: 503
1.307 "Hvala" u 282 poruka
|
|
08. 07. 2009. | #66 | |
Goran Pilipović
Sir Write-a-Lot
|
Evo još jedan query za analizu
Citat:
__________________
Goran Pilipović a.k.a. Ugly Fingers Bradley f.k.a. bluesman I don't always know what I'm talking about but I know I'm right! |
|
08. 07. 2009. | #67 |
expert
Grand Master
|
vi to ozbiljno???
|
08. 07. 2009. | #68 |
majstor
Wrote a book
|
Cekaj jbte.. hoces reci da je neko napisao ovaj query? Mislim rucno?? Ne vjerujemmmmm ja sam nakon 10 redova izgubio pojam sta on hoce...
|
08. 07. 2009. | #69 |
VD IT Direktora
Invented the damn thing
Datum učlanjenja: 08.06.2005
Lokacija: Beograd
Poruke: 2.118
Hvala: 503
1.307 "Hvala" u 282 poruka
|
Nema šanse da je ručno, nego mu treba N random slika iz baze i on je nalepio N upita za dovlačenje jedne random slike.
|
08. 07. 2009. | #70 |
Goran Pilipović
Sir Write-a-Lot
|
Mislim da je situacija slična kao do sada - ovo može biti samo neki auto generisani query, ovo živ čovek ne može da napiše u ovom obliku a da se prethodno nije nagutao magic mushrooms
__________________
Goran Pilipović a.k.a. Ugly Fingers Bradley f.k.a. bluesman I don't always know what I'm talking about but I know I'm right! |
|
|
Slične teme | ||||
Tema | Početna poruka teme | Forum | Odgovori | Poslednja poruka |
Najgori hosting provajderi | ljtruba | Web Hosting, web serveri i operativni sistemi | 144 | 01. 10. 2011. 21:56 |
spor query | ivanhoe | SQL baze podataka - Sponzor: Baze-Podataka.net | 1 | 31. 07. 2007. 19:47 |
sql query | bluesman | SQL baze podataka - Sponzor: Baze-Podataka.net | 19 | 18. 04. 2007. 07:47 |