alias - MySQL query extreme slow filtering on CASE WHEN formula -

-

using mysql 5.6 encounter big performance problem when filtering on calculated formula using conditions case when else end syntax.

this sql formula mapped hibernate. around 6000 rows in database.
table foo has indices on columns product , barcode

1. slow 2-16 s

select      count(*)      foo f      (         case when f.product not null 1         else (             case when f.barcode null 0             else  (                 select exists(                     select 1                     product p                     p.barcode = f.barcode                     limit 1                 )             ) end         ) end     ) = 0

explain results:

+----+--------------------+-------+------+-------------------------------+-----+---------+-----+-------+---------------------------------------------------+ | id |    select_type     | table | type |         possible_keys         | key | key_len | ref | rows  |                                             | +----+--------------------+-------+------+-------------------------------+-----+---------+-----+-------+---------------------------------------------------+ |  1 | primary            | f     |  |                               |     |         |     |   700 | using                                       | |  3 | dependent subquery | p     |  | uq_product,ix_product_barcode |     |         |     |  3134 | range checked each record (index map: 0x2008) | +----+--------------------+-------+------+-------------------------------+-----+---------+-----+-------+---------------------------------------------------+

2. fast ~ 0.4 s

select      *      foo f      (case ... end) = 0

explain results identical count query.

first of should try @ output of explain garner more information.

but either way, let's try cleaning query bit , see if can't make use of few indexes. biggest smell thecase xxx = 0; wonder if query parser having trouble making efficient plan , calculating value per row , comparing result 0.

so let's rewrite as:

where f.product null , (     f.barcode null      or exists (select 1 product p p.barcode = f.barcode) )

if not fix problem, try updating index statistics analyze table.


Comments

Post a Comment

Popular posts from this blog

python - TypeError: start must be a integer -

-
i trying draw 5 turtles, getting typeerror on line 25. here code: import turtle wn = turtle.screen() redrose = turtle.turtle() color = input("what background color be?") fillcolor_f = input("what color of rose be?") redrose.hideturtle() redrose.speed(30) redrose.penup() redrose.left(180) redrose.forward(175) redrose.right(90) redrose.forward(30) redrose.right(90) redrose.pendown() def drawrose(red): redrose.color("pink") redrose.fillcolor(fillcolor_f) redrose.fill(true) in range(red): redrose.forward(i) redrose.right(49) in range(5): drawrose(redrose) redrose.penup() redrose.forward(350) redrose.right(144) redrose.pendown() redrose.fill(false) drawrose(50) wn.bgcolor(color) i trying draw 5 roses, produces errors. doing in interactivepython.org. you recursively calling drawrose wrong parameter. on line 23 ( for in range(red): ) expect red integer...
Read more

c# - DevExpress RepositoryItemComboBox BackColor property ignored -

-
i have following code almost works properly; private void cbstatus_drawitem(object sender, listboxdrawitemeventargs e) { string item = e.item string; if (item != null) { switch (item) { case "1": e.appearance.forecolor = color.green; e.appearance.backcolor = color.green; break; case "2": e.appearance.forecolor = color.orange; e.appearance.backcolor = color.orange; break; case "3": e.appearance.forecolor = color.red; e.appearance.backcolor = color.red; break; } } } when dropdown shown, items forecolor correct, backcolor remains whatever backcolor of theme is; i.e. if i've got set dark theme, backcolor dark, cells in gridview, rather being green/orange/red. i've tried setting e.appearance.options.usebackcolor trying set e....
Read more

django - Creating multiple model instances in DRF3 -

-
1) create multiple model instances 1 api call, asked here: how create multiple model instances django rest framework? tried solution named in link, no success. i trying upload multiple files in 1 api call. result: files uploaded (only once overwrote perform_create) 1 instance created (if send 2 files, latter created instance). my code: class fileuploadserializer(serializers.modelserializer): def __init__(self, *args, **kwargs): many = kwargs.pop('many', true) super(fileuploadserializer, self).__init__(many=many, *args, **kwargs) class meta: model = fileupload read_only_fields = ('created', 'datafile', 'owner') class fileuploadviewset(viewsets.modelviewset): queryset = fileupload.objects.all() serializer_class = fileuploadserializer parser_classes = (multipartparser, formparser, ) def perform_create(self, serializer): file_list = self.request.data.getlist('file') ...
Read more