Overleg gebruiker:Lolsimon/wiu2
Onderwerp toevoegenLaatste reactie: 14 jaar geleden door Valhallasw
SQL-code die hetzelfde zou moeten doen (ongetest)
-- Deze query retourneert waarschijnlijk 72305. Check dat even. SELECT page_id FROM page WHERE page_title="Wiu2" AND page_namespace=10; SELECT page_namespace, page_title, max(rev_timestamp) FROM templatelinks LEFT JOIN page ON page_namespace = tl_namespace AND page_title = tl_title LEFT JOIN revision ON rev_page = page_id WHERE tl_from = 72305 GROUP BY page_namespace, page_title HAVING max(rev_timestamp) < 201001010000;
ongetest, natuurlijk. valhallasw 27 jan 2010 01:40 (CET)
SELECT page_namespace, page_title, rev_timestamp
FROM (
SELECT page_namespace, page_title, page_latest
FROM templatelinks
LEFT JOIN page
ON page_id = tl_from
WHERE tl_namespace = 10
AND tl_title="Wiu2"
AND page_latest < (
SELECT rev_id
FROM revision
WHERE rev_timestamp > "20100101000000"
ORDER BY rev_timestamp
LIMIT 1
)
) AS wps
LEFT JOIN revision
ON rev_id=page_latest;
ORDER BY page_namespace, rev_timestamp;
Vergeet de aanhalingstekens om de rev_timestamp niet. Anders duurt het nog langer dan het al duurt :) Ik moet nog even gaan sql-debuggen denk ik...
mysql> explain SELECT page_namespace, page_title, rev_timestamp from (SELECT page_namespace, page_title, page_latest FROM templatelinks LEFT JOIN page ON page_id = tl_from WHERE tl_namespace = 10 AND tl_title="Wiu2" AND page_latest < (SELECT rev_id FROM revision WHERE rev_timestamp > "20100101000000" ORDER BY rev_timestamp LIMIT 1)) as wps LEFT JOIN revision on rev_id=page_latest ORDER BY page_namespace, rev_timestamp; +----+-------------+---------------+--------+----------------------+---------------+---------+------------------------------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------+----------------------+---------------+---------+------------------------------+--------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 159 | Using temporary; Using filesort | | 1 | PRIMARY | revision | eq_ref | rev_id | rev_id | 4 | wps.page_latest | 1 | | | 2 | DERIVED | templatelinks | ref | tl_from,tl_namespace | tl_namespace | 261 | | 271 | Using where; Using index | | 2 | DERIVED | page | eq_ref | PRIMARY | PRIMARY | 4 | nlwiki.templatelinks.tl_from | 1 | Using where | | 3 | SUBQUERY | revision | range | rev_timestamp | rev_timestamp | 16 | NULL | 667200 | Using where; Using index | +----+-------------+---------------+--------+----------------------+---------------+---------+------------------------------+--------+---------------------------------+ 5 rows in set (0.61 sec)
Het resultaat is in ieder geval:
+----------------+-----------------------------------------------------------------------------------+----------------+ | page_namespace | page_title | rev_timestamp | +----------------+-----------------------------------------------------------------------------------+----------------+ | 0 | EASA_Part-66 | 20091126231631 | | 0 | Hendrik_Brugmans | 20091130232122 | | 0 | Lijst_van_personages_uit_The_Tribe | 20091202130706 | | 0 | Koninklijke_Subsidie_voor_de_Schilderkunst | 20091205184235 | | 0 | Erasmushuis_(Anderlecht) | 20091211223245 | | 0 | Lijst_van_afleveringen_van_Naruto | 20091212181120 | | 0 | Zuid-Afrikaanse_militaire_decoraties:_1952-1975 | 20091213164855 | | 0 | Franciscus_van_Maldeghem | 20091214082626 | | 0 | Gerrit_Corver | 20091216234212 | | 0 | Avant-pop | 20091219111921 | | 0 | Renaissance-humanisme | 20091219134146 | | 0 | Jong_Italië | 20091220164925 | | 0 | Wereldkampioenschap_wielrennen_2009/Selecties | 20091220234013 | | 0 | Gerrit_Arie_Lindeboom | 20091222171612 | | 0 | Nederlandse_Patiëntenvereniging | 20091223154824 | | 0 | Leonard_Lord | 20091223160631 | | 0 | Wet_van_Curie-Weiss | 20091223214918 | | 0 | Wet_van_Moseley | 20091224094357 | | 0 | Wijken_van_Stad_Antwerpen | 20091224221330 | | 0 | Prof.dr._G.A._Lindeboom_Instituut | 20091225134908 | | 0 | NMR-spectrum_voorspellen | 20091230110255 | | 1 | ORMIT | 20091001170718 | | 1 | Mollie | 20091003124242 | | 2 | AmX | 20060126193033 | | 2 | Diablo | 20060218134028 | | 2 | Finrod/Sjabloon:Koningen_van_Númenor | 20060826184239 | | 2 | Finrod/kladblok | 20060920150304 | | 2 | Migiloviz | 20061009145607 | | 2 | Tubantia/Anatomie/Spieren | 20061106214511 | | 2 | Hsf-toshiba/Motto | 20061115201713 | | 2 | Fruitcake/Ordening | 20061210205358 | | 2 | Haarajot/Johan_Maelwael | 20061211212104 | | 2 | Tdevries/Koninklijke_Familie | 20070131224111 | | 2 | Sietske/handig | 20070331112235 | | 2 | Vincentsc/pragmatisch | 20070402133900 | | 2 | MSI-Maxx/pagina/overmij | 20070721165948 | | 2 | MSI-Maxx/pagina/php | 20070722212409 | | 2 | MSI-Maxx/pagina/muziek | 20070722212445 | | 2 | Baldrick/Huidige_Projecten | 20070826021127 | | 2 | Erwin1990/test | 20080125084300 | | 2 | Jayne/Kladblok/Tony_Spinner | 20080125143435 | | 2 | Dinsdagskind/Bibliotheekje | 20080202140740 | | 2 | Al/Roemenië | 20080219123757 | | 2 | Al/Bijdragen | 20080220013234 | | 2 | JeroenvanVeen/Oud | 20080223142952 | | 2 | Darryl_F. | 20080307162804 | | 2 | BertJanWolfs/Portaal:Paddenstoelen | 20080320141515 | | 2 | Hans_Kamp/Tabel1 | 20080325145329 | | 2 | C-man/kladpagina | 20080330212041 | | 2 | Peter_b/mijn_bronnen | 20080404151313 | | 2 | Robert_Prummel/Archiefkast:Overleg_en_commentaar_van_7_october_2006_tot_juni_2007 | 20080405195222 | | 2 | S.G.Hoekstra | 20080429094325 | | 2 | Balko_Kabo/Pogo_(stripverhaal) | 20080522140344 | | 2 | Crazy | 20080627162232 | | 2 | FabioDekker/Zandbak | 20080628233330 | | 2 | Theo/Stef_van_der_Sluis | 20080704202548 | | 2 | Thejermaine/Notitieblaadje | 20080720185550 | | 2 | Zanaq/Darmok | 20080723003703 | | 2 | JPWfriesland/proef_economie_vs | 20080802153318 | | 2 | Maurits/Der_Begriff_der_Religion_im_System_der_Philosophie | 20080809140129 | | 2 | Jayne/Zandbak | 20080812145226 | | 2 | MarieClairette/Jozef_Rulof | 20080824111800 | | 2 | Carsrac/voorendoorbzwclienten/voorbeeldnotulen | 20081017092021 | | 2 | Rubentj_1/Geografie | 20081018135054 | | 2 | Bthv/Zandbak | 20081022232321 | | 2 | Michieldewit | 20081104115005 | | 2 | Pjetter/Zwitsers_rechtssysteem | 20081104220521 | | 2 | Jan_D._Berends/zandbak | 20081111104540 | | 2 | Romaine/Archief/Sjablonen | 20081114163744 | | 2 | Kleuske/WHDB | 20081115163742 | | 2 | Rembert_Andy/Hôtel-Dieu_van_Beaune | 20081205193459 | | 2 | Toby/kladje/Anne_Conway | 20081206140855 | | 2 | Jayne/Kladblok/Rich_Wyman | 20081207185755 | | 2 | Ninane/Lijst_van_vlaggen_van_Zuid-Afrikaanse_deelgebieden | 20081221082315 | | 2 | MigGroningen/Openheid_over_Irak | 20090104230214 | | 2 | SvenDK/5 | 20090302185233 | | 2 | Camaris | 20090318171146 | | 2 | LimoWreck/wraptest | 20090318172207 | | 2 | Baldrick/Mijn_Zandbak2 | 20090326200857 | | 2 | Onderwijsgek/Tijdlijn_van_de_geschiedenis_van_Schoonebeek | 20090412182330 | | 2 | BlackHole/Mobile_codes | 20090504142239 | | 2 | Forrestjunky/kladblok3 | 20090521142818 | | 2 | Theo/Nederlandse_personenautopark_naar_merk | 20090526133409 | | 2 | Ninane/Lijst_van_vlaggen_van_Bulgaarse_deelgebieden | 20090530010943 | | 2 | Ninane/Lijst_van_vlaggen_van_Zweedse_deelgebieden | 20090530011051 | | 2 | Walter_Holden-Belmont/Nationale_Parken/1 | 20090530102230 | | 2 | Noorse/Zolder | 20090531081856 | | 2 | Walter_Holden-Belmont/Nationale_Parken/2 | 20090603083515 | | 2 | Walter_Holden-Belmont/Nationale_Parken/3 | 20090603083603 | | 2 | Walter_Holden-Belmont/Nationale_Parken/4 | 20090603104815 | | 2 | Oskkar/concepten2 | 20090615144313 | | 2 | MoiraMoira/DR-klad | 20090626111305 | | 2 | ElKonquistador/Leuven_Vlaams | 20090707192418 | | 2 | ElKonquistador/Sjabloon | 20090707192418 | | 2 | WDVLWD | 20090712114853 | | 2 | MMaerkk/Helpdesk | 20090720213250 | | 2 | Toby/kladje/kladje | 20090729085330 | | 2 | Rubentj_1/Bijdragen/2008 | 20090731145326 | | 2 | Sumurai8/kladblok/541 | 20090816115424 | | 2 | Jessyboy12/klad | 20090823144931 | | 2 | SchreyP/Zandbak2 | 20090823221311 | | 2 | SchreyP/Zandbak | 20090823224325 | | 2 | Pvt_pauline/Onderlinge_'s-Gravenhage | 20090825093031 | | 2 | Neozoon/Ernst_Jansen_Steur | 20090901225723 | | 2 | Jitalmere | 20090906101612 | | 2 | Isofms.Laura | 20091007114955 | | 2 | Foxie001/zandbak | 20091007143716 | | 2 | Tubantia/Migraine | 20091013233403 | | 2 | Al/Calarasi | 20091018092555 | | 2 | LimoWreck/Politiezones_in_België | 20091019161518 | | 2 | DimiTalen/Voorstel | 20091019202747 | | 2 | Olivier_Bommel/Archief1 | 20091023164203 | | 2 | Daniel575/Joodse_boeken | 20091029100344 | | 2 | Maurits/Phaedo_(Plato) | 20091029140546 | | 2 | SvenDK/6 | 20091029182057 | | 2 | Kanman | 20091111195549 | | 2 | Andre_Engels/Relatief_korte_pagina's | 20091113135241 | | 2 | Mark_knoester | 20091121174000 | | 2 | HHahn/WIU3 | 20091208221711 | | 2 | HHahn/WIU4 | 20091210120043 | | 2 | Angelo.godeau/Conclaaf | 20091214222622 | | 2 | D.A._Borgdorff/Bibliografie | 20091224151250 | | 2 | Siebrand/wachtkamer | 20091227235312 | | 2 | Cars_en_travel/Talen_in_Indonesië | 20091229150302 | | 3 | SgymSdam | 20061211150256 | | 3 | Uraniawizard | 20061213145026 | | 3 | Niels/Archief4 | 20071104203353 | | 3 | TheFreak | 20080219225615 | | 3 | Den_Hieperboree/Archief_2007 | 20080330220435 | | 3 | BleuQ | 20080719142711 | | 3 | Roelanddelrue | 20080827122959 | | 3 | TCDD | 20081027103846 | | 3 | Viking-nl | 20081117215514 | | 3 | Maniago/Archief3 | 20090101075859 | | 3 | G._Lanting | 20090408000357 | | 3 | Diamant/Archief_apr_2009 | 20090505184729 | | 3 | Lex_Tollenaar/archief_2008 | 20090511150315 | | 3 | Xiutwel | 20090824144830 | | 3 | Andra | 20090831230736 | | 3 | Capaccio/archief01 | 20090910192503 | | 3 | Toth/Archief1 | 20091005144859 | | 3 | MoiraMoira/OverlegArchief13 | 20091015195125 | | 3 | MoiraMoira/OverlegArchief2 | 20091015203018 | | 3 | Eddylandzaat/Zeur_&_Zeikdossier_Robotje | 20091030163140 | | 3 | Verrekijker | 20091106212102 | | 3 | MartinD/Archief_overleg_tm_februari_2008 | 20091114120717 | | 3 | Jan_van_reusel/Vooruitgroep | 20091121211937 | | 3 | Atalanta | 20091215221020 | | 3 | Maurits90 | 20091215224751 | | 4 | Verstorend_bewerkgedrag | 20090818192043 | | 4 | Wikiproject/The_Bill/MIT | 20090828115935 | | 4 | Wikiproject/The_Bill/Burnside | 20091030204750 | | 4 | Humor_en_onzin/Wikihippie | 20091124213726 | | 5 | Benoemen_van_een_pagina | 20091204202607 | | 10 | Lijst_van_voormalige_watermolens_in_Zuid-Holland/Data | 20090326063521 | | 10 | Wiu2 | 20091019134252 | | 10 | Filmproject | 20091020212701 | | 100 | Filipijnen/Filipijnse_fauna/Vogels/Eiland | 20091128141219 | | 100 | Middeleeuwen/werkplaats/Honderdjarige_Oorlog | 20091213140344 | +----------------+-----------------------------------------------------------------------------------+----------------+ 159 rows in set (4 min 15.23 sec)
Of, als wikitable: