using indices with SMALL tables
I am regularly using indices on medium-big tables (1000 to > 5 entries), and even on temporary tables (which I use a lot) in joins (EXPLAIN SELECT is your friend). But I'd never thought indices were needed for small tables (100-200 entries). I recently found they are useful too, and I'd like to share. I have one largish table (~5 entries) for which I have to compute some probabilities and likelihoods which depend on two columns, distance d and magnitude mag. While the dependency on d is given by a simple formula, the dependency on mag requires a lookup and a linear interpolation in another SMALL table. The small table has 190 elements. I created a stored function to do the lookup and interpolation. create function lookup (x float) returns float begin declare yr float default -1.0; declare y1 float default 0; declare y2 float default -1.0; declare x1 float default 0; declare x2 float default 0; select mag,y from xyview where mag > x limit 1 into x2,y2; select mag,y from xyview where mag < x order by mag desc limit 1 into x1,y1; set yr=y1 ; if x1 <> x2 then set yr = yr + (x-x1)*(y2-y1)/(x2-x1) ; end if; return yr; Then I attempted to update the big table with statements like update t set lr1 = lookup(mag)*exp(-0.5*d*d)/2/pi() ; This was taking a long time, despite the fact that an explain select t.*,lookup(mag) shows nothing peculiar. I found that a single lookup call takes 0.05 sec, and scaling that for 5 elements would take 38 min. And at the end, I'd have to repeat the process for 48 times (each time changing the table xyview, since prepared statements are not allowed in stored functions). Well, it is enough to add an index on mag on the small table xyview, to cut the processing time BY A FACTOR 736. Now what had taken 38 minutes takes 3.02 sec !!! Great ! -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html Do not like Firefox >=29 ? Get Pale Moon ! http://www.palemoon.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: utf8 options under Mysql
Hi, On 22/04/2016 04:50, Martin Mueller wrote: MySQL has a bewildering variety of unicode collation choices. Most of them are language specific, but what is the difference between "utf8-general-ci", "utf8-unicode-ci", and "utf8-unicode-520-ci." Do they differ in the range of characters they can handle or is it just a matter of the cort order. I understand that utf8-bin is different because it is case sensitive, but the other differences elude me. Under what circumstances does it make a difference to use on or the other? I work with a lot of Early Modern print data and the weird symbols of various kinds they use. I've had trouble at times with the "utf8-general-ci" setting, but it may have been more a matter of settings on my front end tool than of the choice of this rather than unicode collation. Under character sets, there is just one utf8 setting. The simplest way to make sense of the choices would be to say that given a character set (utf8) the collation only makes a difference to the sort but makes no difference to what can be displayed. Is that correct. A collation contains definitions for sorting order and comparison. For most purposes one wants "crème brûlée" to be the same as "creme brulee". For unicode characters these rules can be complex. A character set (in your case UTF-8) defines which character can be stored. utf8-general-ci contains a simplified version of those conversion rules. It works for a lot of Western European languages very well, but in some cases there are problems. For Asian languages there are a lot more problems. For example, 'ß' isn't considered the same as 'ss'. utf8-unicode-ci has more complex rules and works fine for more languages. Due to the more complex rule set it is a bit slower than utf8-general-ci. utf8-unicode-520-ci uses a newer version of the rule set that is used in utf8-unicode-ci. Other utf8-* collations may contain specific rules for specific languages utf8-general-ci is the default collation for utf-8 in MySQL. If you use literal strings MySQL may assume that these have the default collation and comparing them to columns with other collations or performing things like cast operations may produce errors about invalid combinations of collations. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
