Why MemSQL is slower than SQL Server for Select SQL with Substring Operations on Binary Columns -


i have table 2 binary columns used store strings 64 bytes long maximum , 2 integer columns. table has 10+ million rows , uses 2gb of memory out 7gb available memory, there plenty of available memory left. configured memsql based on http://docs.memsql.com/latest/setup/best_practices/.

for simple select sql binary columns compared values, memsql 3 times faster sql server, rule out issues such configuration or hardware memsql.

for complex sqls use

  • substring operations in select clause ,
  • substring , length operations in clause

memsql 10 times slower sql server. measured performance of these sqls on memsql taken after first few runs make sure sql compilation durations not included. looks memsql’s performance issue has how handles binary columns , substring , string length operations.

  1. has seen similar performance issues memsql? if so, column types , sql operations?

  2. has seen similar performance issues memsql substring , length operations on varchar columns?

thanks.

michael,

my recommendation: go rowstore, varbinary instead of binary, consider putting indexes on columns or creating persisted columns, , try rewriting predicate like.
if paste example query, can transform it.

the relevant docs here dev.mysql.com/doc/refman/5.0/en/pattern-matching.html docs.memsql.com/4.0/concepts/computed_columns

good luck.


Comments

Post a Comment

Popular posts from this blog

searchKeyword not working in AngularJS filter -

i writing app using angularjs on front end. want search through table word/field; 1 search box everything. according article here: http://hello-angularjs.appspot.com/searchtable can use filter: searchkeyword this. this code, , not working expected. <label>search: <input ng-model="searchkeyword"></label> <table ng-repeat="post in posts | orderby: sort | filter: searchkeyword"> <tr> <td> {{index(post)}} </td> <td> {{post.title}} </td> <td> {{post.author}} </td> <td> {{post.content}} </td> <td> {{post.date | date: "d/m/yyyy"}} </td> </tr> </table> what should do? thank you try this: controller $scope.posts = [ {title:'title one', author:'author one', content: 'content one'}, {title:'title two', author:'author two', content: 'cont...
Read more

sequelize.js - Sequelize: sort by enum cases -

i have model ( article ) field (the name type ) of type enum('source', 'translated') , return 1 article ordered type field. source articles should returned before translated articles. like: article.findone(order: [ { type: [ 'source', 'translated' ] } ]); unfortunately there appears no built-in solution sorting enums. instead have sort by case , couldn't find documentation on how sequelize. what conventional approach here? sorting case hard in sequelize, sort field (the second answer in linked post) should possible: order: sequelize.fn('field', sequelize.col('type'), 'source', 'translated') it's supported mysql far know though
Read more

user interface - how to replace an ongoing process of image capture from another process call over the same ImageLabel in python's GUI TKinter -

i trying use following code create interactive gui changes color-space(hsv, rbg, grayscale etc) on event of button click. display opencv video in tkinter using multiprocessing being new python having problems multiprocessing , attempts on making gui change it's color space on button clicks hangs entire system. on it's implementation highly appreciated. thankyou. below code: import cv2 pil import image,imagetk import tkinter tk import numpy np multiprocessing import process , queue def quit_it(root,process): root.destroy() process.terminate() def black_andwhite(root,process): process.terminate p=process(target=capture_image, args=(5,queue, )) p.start() root.after(0, func=lambda: update_all(root, image_label, queue)) def update_image(image_label, queue): frame = queue.get() = image.fromarray(frame) b = imagetk.photoimage(image=a) image_label.configure(image=b) image_label._image_cache = b root.update() def update_all(root...
Read more