XF 2.2 Same query different results (PMA - Debug)

[FloV]

Hi there,

i got this query:

SELECT user_id, content_id, content_type, action FROM xf_news_feed WHERE content_type = 'thread' OR content_type = 'post' AND event_date >= 1643820821

The result in PMA gives me 26132 rows but the result in XF debug mode gives me 288754. Why is this so?

PMA:


XF:


Any idea?

Thanks in advance

 

[briansol]

might be interpreting the OR's and And's differently.

Suggest you try wrapping the ORs in parens

 

[Chris D]

I think it's because the query is ambiguous and it may be the case that PMA is parsing it differently, or there's an underlying difference in the mysql libraries in use by the two pieces of software.

Essentially the query could mean you want the thread content type for any event date OR you want the post content_type for event_date >= <date>.

If that makes sense... so it's returning the thread records with no date constraint, and only applying the date constraint to the post records.

What I think you mean is you want all thread records and all post records and only since <date>.

To get that reliably, you need to make a change to the query.

Either (note the parentheses):

SELECT user_id, content_id, content_type, action FROM xf_news_feed WHERE (content_type = 'thread' OR content_type = 'post') AND event_date >= 1643820821

Or our mostly stylistic preference would be:

SELECT user_id, content_id, content_type, action FROM xf_news_feed WHERE content_type IN('thread', 'post') AND event_date >= 1643820821

 

[FloV]

Hi Chris,

thanks for your clarification!

Following to this, i have a new "problem".



Could you explain where there is such a difference in running time and rows just by changing the xf_news_feed table from innodb to myISAM?

 

[Sim]

Could you explain where there is such a difference in running time and rows just by changing the xf_news_feed table from innodb to myISAM?

Your screenshots seem to indicate that you are missing an index?

The first is considering "ALL" rows (and using "where"), while the second is considering a "range" or rows (and using "index condition").

So it seems like the original Innodb table didn't have an appropriate index on event_date and so was doing a table scan of all rows?