| Autor | Zpráva | ||
|---|---|---|---|
| peta Profil |
#1 · Zasláno: 7. 1. 2013, 15:06:54
Melo by to vybrat publikace, podle autoru, vytridit podle kategorie (struktura s1,s2) a datumu.
Vsechny textove sloupce jsou typu text, protoze kazdy rok se meni pocty znaku, tak to puvodni tvurce neresil. i ten dotaz je jeho dilo. Ja jen vim, ze like neni dobre takto kombinovat. Ono se to ukaze uz pri 6 slovech, kdy je odezva asi 20s :) query = SELECT Articles.* FROM Articles,Authors,ArtAut,ArtAut AS art1,Authors AS aut1,ArtAut AS art2,Authors AS aut2,ArtAut AS art3,Authors AS aut3,ArtStru JOIN (StruStru AS S1,StruStru AS S2) ON ( (S1.referenceid=ArtStru.StruAid AND ( S1.referenceid=2)) OR (S1.lowerid=ArtStru.StruAid AND ( S1.referenceid=2)) OR (S1.lowerid=ArtStru.StruAid AND ( S2.higherid=2) AND S2.referenceid=S1.referenceid)) WHERE title IS NOT NULL AND ArtAut.ArtAid=art1.ArtAid AND ArtAut.ArtAid=art2.ArtAid AND ArtAut.ArtAid=art3.ArtAid AND (ArtAut.AutAid=Authors.IDAuthor AND Authors.secondname LIKE '%aa%') AND (art1.AutAid=aut1.IDAuthor AND aut1.secondname LIKE '%bb%') AND (art2.AutAid=aut2.IDAuthor AND aut2.secondname LIKE '%cc%') AND (art3.AutAid=aut3.IDAuthor AND aut3.secondname LIKE '%dd%') AND ArtAut.ArtAid=Articles.IDArticle AND ArtStru.ArtSid=Articles.IDArticle AND publishdate>='2000-01-01' AND publishdate<='2013-12-31' GROUP BY Articles.IDArticle ORDER BY publishdate DESC |
||
| Kajman Profil |
#2 · Zasláno: 7. 1. 2013, 15:40:09
Pokud hledáš publikace, kde jsou všichni zadaní spoluautoři, zkus obdobu z faq.
|
||
| peta Profil |
Zkoumal jsem reseni s REGEXP, ale dela to neco jineho, zaznamu mi nevyhodil 45 ale 52. Coz odpovida tomu, ae je tam author 1 nebo 2 ale ne spolecne. Casove to vychazelo uplne stejne 0.2s
SELECT `a`.* FROM Authors u, Articles `a`, ArtAut `au`, ArtStru `as` JOIN (StruStru AS S1,StruStru AS S2) ON ( (S1.referenceid=`as`.StruAid AND ( S1.referenceid=2)) OR (S1.lowerid=`as`.StruAid AND ( S1.referenceid=2)) OR (S1.lowerid=`as`.StruAid AND ( S2.higherid=2) AND S2.referenceid=S1.referenceid) ) WHERE u.secondname REGEXP 'bakala|stuchlik' AND u.IDAuthor=au.AutAid AND `a`.title IS NOT NULL -- AND u1.ArtAid=u2.ArtAid AND `a`.IDArticle=`au`.ArtAid AND `a`.IDArticle=`as`.ArtSid AND publishdate>='2000-01-01' AND publishdate<='2013-12-31' GROUP BY `a`.IDArticle Pak jsem si rikal, to tedy ne. Zmenil jsem engine z inno na myisam a pridal fulltext index nad secondname a zmenil to na matchy. Ted mi to trva 0.03s. Myslim, ze s tim vic carovat asi nepujde. 2011-10-26 - 2007-01-01] SELECT Articles . * FROM Articles, Authors, ArtAut, ArtAut AS art1, Authors AS aut1, ArtStru JOIN ( StruStru AS S1, StruStru AS S2 ) ON ( ( S1.referenceid = ArtStru.StruAid AND ( S1.referenceid =2 ) ) OR ( S1.lowerid = ArtStru.StruAid AND ( S1.referenceid =2 ) ) OR ( S1.lowerid = ArtStru.StruAid AND ( S2.higherid =2 ) AND S2.referenceid = S1.referenceid ) ) WHERE title IS NOT NULL AND ArtStru.ArtSid = Articles.IDArticle AND publishdate >= '2000-01-01' AND publishdate <= '2013-12-31' AND ArtAut.ArtAid = art1.ArtAid AND ( ArtAut.AutAid = Authors.IDAuthor AND MATCH ( Authors.secondname ) AGAINST ( 'bakala' ) ) AND ( art1.AutAid = aut1.IDAuthor AND MATCH ( aut1.secondname ) AGAINST ( 'stuchlik' ) ) AND ArtAut.ArtAid = Articles.IDArticle GROUP BY Articles.IDArticle ORDER BY publishdate DESC LIMIT 0 , 30 Ten odkaz jsem zkoukl, ale v zasade neresi muj problem, ten dotaz je tam uplne stejny (az na join na zacatku). Navic tam nema like, takze by zkracene reseni ...#7 ani neslo pouzit. Puvodni dotaz s LIKE mi dela 0.2s SELECT Articles.* FROM Articles,Authors,ArtAut,ArtAut AS art1,Authors AS aut1,ArtStru JOIN (StruStru AS S1,StruStru AS S2) ON ( (S1.referenceid=ArtStru.StruAid AND ( S1.referenceid=2)) OR (S1.lowerid=ArtStru.StruAid AND ( S1.referenceid=2)) OR (S1.lowerid=ArtStru.StruAid AND ( S2.higherid=2) AND S2.referenceid=S1.referenceid)) WHERE title IS NOT NULL AND ArtStru.ArtSid=Articles.IDArticle AND publishdate>='2000-01-01' AND publishdate<='2013-12-31' AND ArtAut.ArtAid=art1.ArtAid AND (ArtAut.AutAid=Authors.IDAuthor AND Authors.secondname LIKE '%bakala%') AND (art1.AutAid=aut1.IDAuthor AND aut1.secondname LIKE '%stuchlik%') AND ArtAut.ArtAid=Articles.IDArticle GROUP BY Articles.IDArticle ORDER BY publishdate DESC |
||
| Kajman Profil |
#4 · Zasláno: 8. 1. 2013, 10:42:39
peta:
„ae je tam author 1 nebo 2 ale ne spolecne“ To "společně" přávě řeší odkazované FAQ. „Zmenil jsem engine z inno na myisam“ Innodb asi mělo nějaký význam. V tom případě se dělá jen kopie sloupečku do jiné tabulky typu myisam (může se navíc oddělat i diakritika) pro potřeby fulltextu. A fulltext najde jen celá slova, like i část. |
||
| peta Profil |
#5 · Zasláno: 8. 1. 2013, 12:29:02
Nemelo. To delali studenti, ti maji pulku tabulek myisam, druhou inno. Zavislosti si nehlidaji sql prikazy, ale primo v programu. At uz pouzili inno z jakehokoliv duvodu, tak urcite ne pro inno vlastnosti, spis s hecu, ze jim to nekdo nakukal jako nejlepsi.
|
||
|
Časová prodleva: 11 let
|
|||
0