casting - MySQL CAST sure takes its sweet time -

-

so ran following in mysql console control test see holding speed of query.

select bbva_deductions.ded_code, substring_index(bbva_deductions.employee_id, '-' , -1) tt_emplid,              bbva_job.paygroup, bbva_job.file_nbr, bbva_deductions.ded_amount              bbva_deductions              left join bbva_job              on cast(substring_index(bbva_deductions.employee_id, '-' , -1) unsigned) = bbva_job.emplid limit 500

it took consistently around 4 seconds run. (seems high 500 rows).

simply removing cast part of join decreased 0.01 seconds...

why on earth mr.cast slow? doing anger mysql gods here?

edit:

as requested here explain output:

enter image description here

and without cast:

enter image description here

explain extended:

enter image description here

as documented under how mysql uses indexes:

mysql uses indexes these operations:

[ deletia ]

  • to retrieve rows other tables when performing joins. mysql can use indexes on columns more efficiently if declared same type , size. in context, varchar , char considered same if declared same size. example, varchar(10) , char(10) same size, varchar(10) , char(15) not.

    comparison of dissimilar columns may prevent use of indexes if values cannot compared directly without conversion. suppose numeric column compared string column. given value such 1 in numeric column, might compare equal number of values in string column such '1', ' 1', '00001', or '01.e1'. rules out use of indexes string column.

in case, attempting join on comparison between substring (of string column in 1 table) , string column in table. index can used operation, comparison performed lexicographically (i.e. treating operands strings, if represent numbers).

by explicitly casting 1 side integer, comparison performed numerically (as desired) - requires mysql implicitly convert type of string column , therefore unable use column's index.

you have hit roadbump because schema poorly designed. should strive ensure columns:

  1. are encoded using data types relevant content; and

  2. contain single piece of information—see is storing delimited list in database column bad?

at least, bbva_job.emplid should integer; , bbva_deductions.employee_id should split parts stored in separate (appropriately-typed) columns. appropriate indexes, query considerably more performant.


Comments

Post a Comment

Popular posts from this blog

javascript - DIV "hiding" when changing dropdown value -

-
this related previous question asked: hide / show multiple divs i have code in place previous question , seems work ok apart when change value in dropdown "after" ticket selection made. i have number of javascrpts in place wondering if there clash somewhere? first bit of code in head of document. <head> <script type="text/javascript"> $(function() { $('.cat_dropdown').change(function() { $('#paymethod').toggle($(this).val() >= 2); }); }); </script> <script type="text/javascript"> $(document).ready(function () { $(".paymentmethod").click(function () { $(".paymentinfo").hide(); switch ($(this).val()) { case "credit card authorisation": $("#pay0").show("slow"); break; ...
Read more

html - Accumulated Depreciation of Assets on php -

-
i have problem table print out asset accumulated depreciation on php, stright line method. $query = mysqli_query($connect, "select current_value, salvage_value, useful_life, placed_service assets"); if(!$query){ echo 'could not run query b: ' . mysqli_error($connect); exit; } while($asset = mysqli_fetch_row($query)){ echo "assets :"; print_r($asset); $month = 12; $year = date('y',strtotime($asset[3])); $first_year = $month - ((int)date('m',strtotime($asset[3]))-1); $last_year = $month - $first_year; $first = round((($asset[0] - $asset[1])/$asset[2])*(($first_year)/12),0); $last = round((($asset[0] - $asset[1])/$asset[2])*(($last_year)/12),0); $others = ($asset[0] - $asset[1])/$asset[2]; echo "<table border=1>"; echo "<tr><th>year</th><th>book value year start</th><th>depreciation expense</th><th...
Read more

Update QSQLTableModel in QT C++ -

-
i created 1 dialog used bindvalues in sql database, , can save every new record sucessfully. problem i've got dialog qsqltablemodel shows information database. every time make new record must close program , run again see changes in showinformationdialog() . know way such don't need close program update information in showinformationdialog() ? you can use select() method of qsqltablemodel class repopulate model data database. bound views updated automatically. suggest connecting signal wherever update data in database (i.e. dialog updating sql database) custom slot in showinformationdialog() calls select() of qsqltablemodel object. assume have qmainwindow object parent of both dialogues best place establish connection. also since showinformationdialog() 's model used viewing data consider using qsqlquerymodel . if used setquery() method should used instead of select() otherwise (the connection of signal showinformationdialog() 's slot) same. ...
Read more