Post reply

Reply to the message by Joe I

Name:

E-mail: (optional, won't be displayed directly)

Leave this field empty:

Homepage: (optional)

Leave this field empty:

Location: (optional)

Remember me (cookie)

Category:

Subject:

Formatting help

skip to input

format text bold: [b]bold text[/b]
format text italic: [i]italic text[/i]
insert hyperlink: [link=http://example.com/]link text[/link] / [link]http://example.com/[/link]
set text color: [color=#rgb]colored text[/color]
font size: [size=small]small text[/size]; [size=large]large text[/size]
insert list: [list][*]list item[/list]
insert image: [img]http://example.com/image.jpg[/img]; left: [img=left]http://example.com/image.jpg[/img]; right: [img=right]http://example.com/image.jpg[/img]; thumbnail: [img=thumbnail]http://example.com/image.jpg[/img]; thumbnail left: [img=thumbnail-left]http://example.com/image.jpg[/img]; thumbnail right: [img=thumbnail-right]http://example.com/image.jpg[/img]
upload image: upload image ...
insert TeX code: [tex]TeX code[/tex]
insert code: [inlinecode]code[/inlinecode]; [code]code[/code]; [code=css]code[/code]; [code=html]code[/code]; [code=javascript]code[/code]; [code=perl]code[/code]; [code=php]code[/code]; [code=sql]code[/code]; [code=xml]code[/code]

:-)
;-)
:-P
:-D
:-|
:-(
:yes:
:no:
:ok:
:lol:
:lol2:
:lol3:
:cool:
:surprised:
:angry:
:crying:
:waving:
:confused:
:lookaround:
:clap:
:love:
:tick:

Message:

> >
> > First questions. More will follow.
> > 
> > > [b] My mods to 20220803.1 are below (note, these are the queries as run through SQL testing. I can post the actual php code changes if desired. The php code mods should have a net zero effect on all board installations, regardless of configuration.[/b]
> > > 
> > > [b]total_spam[/b]
> > > 
> > > The differences between these two are:
> > > [list]
> > > [*]akismet and b8 JOINs are moved into subqueries 
> > > [/list]
> > 
> > It's looking plausible to use pre filtered results for joining. Additionally this removes the [inlinecode]OR[/inlinecode] in the [inlinecode]WHERE[/inlinecode]-clause, that alone can lead to a full table scan instead of using an existing index, from the where clause.
> > 
> Yes, agreed on pre filtering and table scan issues. In this particular case, the above changes can be made without any pre filtering and the performance difference is quite significant.
> 
> > > [b]total_postings[/b]
> > > 
> > > The differences between these two are:
> > > [list]
> > > [*]Add STRAIGHT_JOIN to force order of JOINs
> > > [/list]
> > 
> > Here I have a serious question. This is the first time I read about [inlinecode]STRAIGHT_JOIN[/inlinecode]. But allexamples I can find are using it in the [inlinecode]FROM … JOIN …[/inlinecode] section but not directly after [inlinecode]SELECT[/inlinecode]. Additionally the MySQL documentation states [inlinecode]STRAIGHT_JOIN[/inlinecode] as a replacement for pure [inlinecode]JOIN[/inlinecode] (which itself is a shorthand for [inlinecode]INNER JOIN[/inlinecode]). We are using [inlinecode]LEFT JOIN[/inlinecode]s. Does this not contradict itself?
> > 
> > Tschö, Auge
> >
> Ahh, good question. I will caveat this first by noting that I am running on MariaDB. MySQL has an additional optimizer hint (/*+ JOIN_FIXED_ORDER */) that will also work in forcing the order of the JOINs, but MariaDB has not (yet) implemented this hint. Fort compatibility across both, I'm using STRAIGHT_JOIN.
> 
> Both MySQL and MariaDB allow for use of the STRAIGHT_JOIN in either the SELECT or FROM clauses.
> 
> You are correct in that using the STRAIGHT_JOIN in the FROM clause will act as an INNER JOIN.
> 
> In MariaDB (and my untested understanding for MySQL), using the STRAIGHT_JOIN in the SELECT will simply force the order of the JOINs as specified in the FROM clause, but will not force INNER JOIN for tables in the FROM clause.  I have confirmed this to be true in MariaDB, and the LEFT JOINs still work properly.
> 
> 
> Note: I am still working on performance improvements to 20220803.1.  It's not yet fast enough for us to roll out on our forum, so I'm sticking with my modded 2.4.24 for now.  Performant JOINs on the akismet_rating and b8_rating tables are still eluding me, but I'll keep trying.

E-mail notification on reply of this posting