Hi @rfischmann
I believe we are as close as we can get to the cause of the problem. My initial MySQL query did not detect this situation; it is that the number of words in the document was correctly counted and stored, but in fact the words themselves were not included in the index vector lists.
Below I am attaching an improved version of the query, could you run it and attach the result as before – it will identify all unindexed publications of the first type (like Spirit Awards and 795909).
All I have to do is find out why exactly these publications were not processed properly. I’m already analyzing my code and trying to replicate the problem on my side.
I’m wondering if there are any publications where only the titles were not indexed, but not the content, or vice versa. And also, is the problem related to the length of publications or the presence of special characters in the text (probably this can break queries if the encoding of the database tables does not match).
Thank you!
select * from
(select
p.ID post_id,
wi.id windex_id,
p.post_title,
p.post_type,
p.post_status,
wlog.status,
wd_t.n wdt_n,
dd_t.n_total wdt_total,
dd_t.n_nw wdt_nw,
length(p.post_title) title_len,
(length(p.post_title) / wd_t.n) av_t,
wd_c.n wdc_n,
dd_c.n_total wdc_total,
dd_c.n_nw wdc_nw,
length(p.post_content) content_len,
(length(p.post_content) / wd_c.n) av_c,
(if(isnull(wi.id),128,0) +
if(wlog.status != 3,64,0) +
if((ifnull(dd_t.n_nw, 0) != ifnull(dd_t.n_total, 0)) or (ifnull(dd_t.n_total, 0) != ifnull(wd_t.n, 0)) or (ifnull(dd_c.n_nw, 0) != ifnull(dd_c.n_total, 0)) or (ifnull(dd_c.n_total, 0) != ifnull(wd_c.n, 0)), 32, 0) +
if(((length(p.post_title) > 0) and (wd_t.n = 0)) or ((length(p.post_content) > 0) and (wd_c.n = 0)), 16, 0) +
if(((isnull(wd_t.n) or (wd_t.n < 1)) and (isnull(wd_c.n) or (wd_c.n < 1)) and (p.post_type in ("post", "page"))), 8, 0)) warn_level
from wp_posts p
left join wpftsi_index wi
on p.ID = wi.tid and wi.tsrc = "wp_posts"
left join wpftsi_docs wd_t
on wd_t.token = "post_title" and wd_t.index_id = wi.id
left join wpftsi_docs wd_c
on wd_c.token = "post_content" and wd_c.index_id = wi.id
left join wpftsi_ilog wlog
on wlog.index_id = wi.id
left join (
select
v.did,
sum(if(isnull(w.word),0,1)) n_nw,
count(*) n_total
from wpftsi_vectors v
left join wpftsi_words w
on w.id = v.wid
group by v.did
) dd_t
on dd_t.did = wd_t.id
left join (
select
v.did,
sum(if(isnull(w.word),0,1)) n_nw,
count(*) n_total
from wpftsi_vectors v
left join wpftsi_words w
on w.id = v.wid
group by v.did
) dd_c
on dd_c.did = wd_c.id
) tt
where
tt.warn_level > 0
and
(tt.post_type not in ("oembed_cache"))
order by tt.warn_level desc, tt.post_type desc
limit 10000
