Autor Zpráva
peta
Profil
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
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
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
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.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: