With the introduction of the actor and comment tables, I have seen a notable spike in query times. Take a look at Jessie’s query. A query that used to take a couple of seconds now takes almost 5 minutes. It also spanned what 8-9 different joins now? In database design you should almost never have perfect normalization due to performance issues. I know this point will be ignored, but it’s at least worth mentioning.
On Mon, Jun 3, 2019 at 4:44 PM Chico Venancio <chicocvenan...@gmail.com> wrote: > Hey John, > What is the specific normalization you believe is unneeded? > > > Chico Venancio > > Em seg, 3 de jun de 2019 16:58, John <phoenixoverr...@gmail.com> escreveu: > >> This is a prime example of over normalization. We are normalizing and >> making things slower. >> >> On Mon, Jun 3, 2019 at 3:38 PM Jesse Plamondon-Willard < >> pathosch...@gmail.com> wrote: >> >>> Sure! I just fetch the comment ID in the original query ([1]), and fetch >>> the comment text with a separate query ([2]). You need *n*+1 queries >>> based on the number of records, but the performance is adequate for my use >>> cases. >>> >>> [1]: main query >>> <https://tools.wmflabs.org/sql-optimizer?use=enwiki_p&sql=SELECT%0D%0A++++user_id%2C%0D%0A++++user_registration%2C%0D%0A++++DATE_FORMAT%28user_registration%2C+%22%25Y-%25m-%25d+%25H%3A%25i%22%29+AS+registration%2C%0D%0A++++user_editcount%2C%0D%0A++++GROUP_CONCAT%28ug_group+SEPARATOR+%22%2C+%22%29+AS+user_groups%2C%0D%0A++++ipb_by_text%2C%0D%0A++++ipb_reason_id%2C%0D%0A++++DATE_FORMAT%28ipb_timestamp%2C+%22%25Y-%25m-%25d+%25H%3A%25i%22%29+AS+ipb_timestamp%2C%0D%0A++++ipb_deleted%2C%0D%0A++++COALESCE%28DATE_FORMAT%28ipb_expiry%2C+%22%25Y-%25m-%25d+%25H%3A%25i%22%29%2C+ipb_expiry%29+AS+ipb_expiry%0D%0AFROM%0D%0A++++user%0D%0A++++LEFT+JOIN+user_groups+ON+user_id+%3D+ug_user%0D%0A++++LEFT+JOIN+ipblocks_ipindex+ON+user_id+%3D+ipb_user%0D%0AWHERE+user_name+%3D+%27Pathoschild%27%0D%0ALIMIT+1> >>> [2]: secondary query to fetch comment text >>> <https://tools.wmflabs.org/sql-optimizer?use=enwiki_p&sql=SELECT+comment_text%0D%0AFROM+comment%0D%0AWHERE+comment_id+%3D+1000%0D%0ALIMIT+1> >>> >>> On Mon, 3 Jun 2019 at 14:51, Huji Lee <huji.h...@gmail.com> wrote: >>> >>>> Out of curiosity, Jesse, can you share the query after that change, so >>>> it is all stored in this thread for future reference? >>>> >>> _______________________________________________ >>> Wikimedia Cloud Services mailing list >>> Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) >>> https://lists.wikimedia.org/mailman/listinfo/cloud >> >> _______________________________________________ >> Wikimedia Cloud Services mailing list >> Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) >> https://lists.wikimedia.org/mailman/listinfo/cloud > > _______________________________________________ > Wikimedia Cloud Services mailing list > Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) > https://lists.wikimedia.org/mailman/listinfo/cloud
_______________________________________________ Wikimedia Cloud Services mailing list Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud
