Re: optimizer in function
I think the only clues the optimizer consults with regard to UDFs is the 'characteristic' provided at the time you create the routine. from http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html * * *characteristic*: COMMENT '*string*' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } - michael dykman 2011/10/8 Halász Sándor > Does the optimizer look into function called from query? > > In my queries the expression (SELECT hwyl FROM Stock) / (SELECT > regularPayment FROM Stock), where Stock is a one-record table, often is > repeated. The optimizer sees that, and makes the ratio a constant, and I can > afford to be clear. If that expression were within a function called from > the same spot, would the optimizer look into the function and see the same > effectiv constant? or is it better to make it an argument to the function? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com May the Source be with you.
optimizer in function
Does the optimizer look into function called from query? In my queries the expression (SELECT hwyl FROM Stock) / (SELECT regularPayment FROM Stock), where Stock is a one-record table, often is repeated. The optimizer sees that, and makes the ratio a constant, and I can afford to be clear. If that expression were within a function called from the same spot, would the optimizer look into the function and see the same effectiv constant? or is it better to make it an argument to the function? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query works; query in function doesn't
I have a puzzling situation where a query works, but only outside of a function. If I try to abstract away some of the complexity by placing the subquery inside a function, the db seems to get stuck in an infinite query and the entire system becomes unresponsive (from the MySQL console I can ctrl-c to break out, but from the GUI query browser there is no way to stop it other than hard reset). Anyway, the db is laid out so that a class of elements is kept in one table, while another table keeps instances of the elements. The 'entry' number from the class table is used as a foreign key in the instance table. Makes sense so far, right? The tricky part is that the 'entry' number is just an internal value used as a unique key, so the users of the instance table have to keep looking up the 'name' of the element in order to find out what the 'entry' number is. The idea is to make life easier for them by translating the 'name' string into the 'entry' number via a function. So, here's a raw query string, which works ok: SELECT * FROM tbl1 WHERE id=(SELECT entry FROM tbl2 WHERE name='someguy'); -- returns the correct rows from tbl1 If I take the subquery and put it in a function, which takes the name string as an argument, then make a simple SELECT query to test the function by itself, it returns the correct value, like so: create function getNumber(cName varchar(255)) returns int begin return (select entry from tbl2 where name=cName); end; SELECT getNumber('someguy'); -- returns the correct value Now, when I try to use the function in the full query, that's when the problem state occurs: SELECT * FROM tbl1 WHERE id=getNumber('someguy'); -- disaster Can anybody tell me what's wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the max length of IN() function?
Donny Simonton wrote: In our case we were using words, and phrases, so we would have something like: IN ('a', 'apple', 'apple car', 'car', 'c') etc... We found that once it hits about 200 or so entries the query went from 0.00 seconds to about 2-3 seconds. Sometimes much more. I would guess that it has more to do with the amount of your key space that the list ranges over than with the absolute number of entries. Try comparing "IN ('a', 'z')" (or something similar) with "IN ('a', 'aa', 'aaa', 'aab', [...], 'aaaz')" (with lots of entries, all between 'a' and 'ab', or another small range). MySQL can use a range of the index for the second, even though there are lots of entries, but not for the first, which may force it to scan the whole index. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is the max length of IN() function?
Actually, I've done a test with this in the past, we could not find a limit. But there is a magic number where the optimizer stops doing a good job of optimizing the query and it starts to get really slow. In our case we were using words, and phrases, so we would have something like: IN ('a', 'apple', 'apple car', 'car', 'c') etc... We found that once it hits about 200 or so entries the query went from 0.00 seconds to about 2-3 seconds. Sometimes much more. Donny > -Original Message- > From: Tom Crimmins [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 16, 2005 9:07 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > Subject: RE: What is the max length of IN() function? > > > > -Original Message- > > From: Daevid Vincent > > Sent: Wednesday, February 16, 2005 20:59 > > To: mysql@lists.mysql.com > > Subject: What is the max length of IN() function? > > > > I tried to find this function on the dev.mysql.com site, but good luck > > finding "in"... ;-) > > > > Can someone tell me what the maximum length is for this function? > > > > SELECT * FROM foo WHERE bar IN(1,2,3,4,. N); > > > > How many entries can there be in between 1 and N ? Hundreds? > > Thousands? > > Millions? > > From http://dev.mysql.com/doc/mysql/en/comparison-operators.html, "The > number of values in the IN list is only limited by the max_allowed_packet > value." > > --- > Tom Crimmins > Interface Specialist > Pottawattamie County, Iowa > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is the max length of IN() function?
> -Original Message- > From: Daevid Vincent > Sent: Wednesday, February 16, 2005 20:59 > To: mysql@lists.mysql.com > Subject: What is the max length of IN() function? > > I tried to find this function on the dev.mysql.com site, but good luck > finding "in"... ;-) > > Can someone tell me what the maximum length is for this function? > > SELECT * FROM foo WHERE bar IN(1,2,3,4,. N); > > How many entries can there be in between 1 and N ? Hundreds? > Thousands? > Millions? >From http://dev.mysql.com/doc/mysql/en/comparison-operators.html, "The number of values in the IN list is only limited by the max_allowed_packet value." --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is the max length of IN() function?
I tried to find this function on the dev.mysql.com site, but good luck finding "in"... ;-) Can someone tell me what the maximum length is for this function? SELECT * FROM foo WHERE bar IN(1,2,3,4,. N); How many entries can there be in between 1 and N ? Hundreds? Thousands? Millions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.1.7 make error "manager.c: In function `mysql_manager_connect':"
Hello, Sorry for the cross post, I didn't get any response from the internals list... I'm trying to compile mysql 4.1.7 from source on SLES 8, running on os390. I'm having a problem that others have seemed to have: http://lists.mysql.com/internals/17577 http://www.linuxquestions.org/questions/history/225560 http://forums.mysql.com/read.php?11,6459,6459 http://lists.mysql.com/internals/3838 But the fixes that were suggested were not clear, and in some cases, like for 17577, I tried adding the references to the other header files, but it did not fix the problem. If anyone can be of assistance, it would greatly be appreciated. -Thanks, RYAN #!/bin/sh ./configure \ --prefix=/usr/local/mysql-4.1.7 \ --localstatedir=/usr/local/mysql/data \ --without-debug gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql-4.1.7\" -DDATADIR=\"/usr/local/mysql/data\" -DSHAREDIR=\"/usr/local/mysql-4.1.7/share/mysql\" -DUNDEF_THREADS_HACK -DDONT_USE_RAID -I. -I. -I.. -I../include -O3 -DDBUG_OFF -MT manager.lo -MD -MP -MF .deps/manager.Tpo -c manager.c -fPIC -DPIC -o .libs/manager.o manager.c: In function `mysql_manager_connect': manager.c:136: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type manager.c:136: too few arguments to function `gethostbyname_r' manager.c:136: warning: assignment makes pointer from integer without a cast make[2]: *** [manager.lo] Error 1 make[2]: Leaving directory `/admin/packages/cbtap/mysql-4.1.7/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/admin/packages/cbtap/mysql-4.1.7' make: *** [all] Error 2 24 #include "mysql.h" 25 #include "mysql_version.h" 26 #include "mysqld_error.h" 27 #include 28 #include 29 #include 30 #include 31 #include 32 #include 33 #include 34 #include 35 #include 36 #include 37 #include 130 else 131 { 132 int tmp_errno; 133 struct hostent tmp_hostent,*hp; 134 char buff2[GETHOSTBYNAME_BUFF_SIZE]; 135 hp = my_gethostbyname_r(host,&tmp_hostent,buff2,sizeof(buff2), 136 &tmp_errno); 137 if (!hp) 138 { 139 con->last_errno=tmp_errno; 140 sprintf(con->last_error,"Could not resolve host '%-.64s'",host); 141 my_gethostbyname_r_free(); 142 goto err; 143 } 144 memcpy(&sock_addr.sin_addr,hp->h_addr, (size_t) hp->h_length); 145 my_gethostbyname_r_free(); 146 } -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN function
Yes --Original Message- > Hi, > > I have written a program that creates statments for me, and sometimes I > end > with > SELECT..WHERE number IN(1) > instead of > SELECT... WHERE number IN(1,2,3,4) > would number IN(1) works, > for the moment i have no mean to test it, it is why i'm asking. > > Regards, > Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IN function
Hi, I have written a program that creates statments for me, and sometimes I end with SELECT..WHERE number IN(1) instead of SELECT... WHERE number IN(1,2,3,4) would number IN(1) works, for the moment i have no mean to test it, it is why i'm asking. Regards, Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[log.cc error in function stops mysqld on every connection!!]
>Description: >How-To-Repeat: >Fix: >Submitter-Id: >Originator:angelos Vasdaris >Organization: >MySQL support: [none] >Synopsis: >Severity: >Priority: >Category: mysql >Class: >Release: mysql-3.23.46-max (Source distribution) >Environment: System: SunOS talos 5.8 Generic_108529-12 i86pc i386 i86pc Architecture: i86pc Some paths: /usr/bin/perl /usr/local/bin/make /usr/local/bin/gmake /usr/local/bin/gcc /usr/local/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/i386-pc-solaris2.8/3.0.2/specs Configured with: ../configure --with-as=/usr/local/bin/as --with-ld=/usr/local/bin/ld Thread model: posix gcc version 3.0.2 Compilation info: CC='gcc' CFLAGS='-O3 -fomit-frame-pointer -DHAVE_CURSES_H' CXX='gcc' CXXFLAGS='-O3 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -DHAVE_CURSES_H' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1588040 Dec 4 02:51 /lib/libc.a lrwxrwxrwx 1 root root 11 Nov 29 11:09 /lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 root bin 943052 Dec 4 02:51 /lib/libc.so.1 -rw-r--r-- 1 root bin 1588040 Dec 4 02:51 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Nov 29 11:09 /usr/lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 root bin 943052 Dec 4 02:51 /usr/lib/libc.so.1 Configure command: ./configure --with-mysql-user=mysql --enable-assembler --prefix=/usr/local/mysql-3.23.46-max --with-server-suffix=-max --with-innodb --enable-shared --enable-static --with-debug=yes --with-gnu-ld (backtrace full from gdb) #0 0x80a6184 in _ZN9MYSQL_LOG5writeEP3THD19enum_server_commandPKcz (this=???, thd=???, command=???, format=???) at log.cc:561 this = (class MYSQL_LOG *) 0x8aa1bd0 thd = (THD *) 0x0 error = 145365980 uname -a -> SunOS talos 5.8 Generic_108529-12 i86pc i386 i86pc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: gcc 2.95.3 compile failure -"sql_lex.cc: In function `void lex_init()':"
[EMAIL PROTECTED] writes: > >Description: > > I am using the recommended ./configure options as specified by: > http://www.mysql.com/doc/S/o/Solaris.html. I am unsure how to > specifiy the GNU ld - the output from ./configure shows that I am > using the Solaris ld. Perhaps that is the problem? I run GNU make 3.79 > and GNU m4 1.4, GNU Autoconf 2.13, Automake 1.4. ./configure > knows I am using the other GNU tools bison and flex. > All attempts to compile MySQL 3.23.39 using GCC 2.95.2 or 2.95.3 fail > with this error: > These symbols are defined in lex.h. Check out that you have that file and that it is OK. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
gcc 2.95.3 compile failure -"sql_lex.cc: In function `void lex_init()':"
>Description: I am using the recommended ./configure options as specified by: http://www.mysql.com/doc/S/o/Solaris.html. I am unsure how to specifiy the GNU ld - the output from ./configure shows that I am using the Solaris ld. Perhaps that is the problem? I run GNU make 3.79 and GNU m4 1.4, GNU Autoconf 2.13, Automake 1.4. ./configure knows I am using the other GNU tools bison and flex. All attempts to compile MySQL 3.23.39 using GCC 2.95.2 or 2.95.3 fail with this error: make[3]: Entering directory `/usr/local/src/mysql-3.23.39/sql' gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME="\"/usr/local/mysql\"" -DDATADIR="\"/usr/local/mysql/var\"" -DSHAREDIR="\"/usr/local/mysql/share/mysql\"" -DHAVE_CONFIG_H -I./../include -I./../regex-I. -I../include -I.. -I.-O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_CURSES_H -I/usr/local/src/mysql-3.23.39/include -DHAVE_RWLOCK_T -c sql_lex.cc sql_lex.cc: In function `void lex_init()': sql_lex.cc:85: `symbols' undeclared (first use this function) sql_lex.cc:85: (Each undeclared identifier is reported only once sql_lex.cc:85: for each function it appears in.) sql_lex.cc:87: `sql_functions' undeclared (first use this function) sql_lex.cc: In function `int find_keyword(LEX *, unsigned int, bool)': sql_lex.cc:168: implicit declaration of function `int get_hash_symbol(...)' sql_lex.cc:168: initialization to `SYMBOL *' from `int' lacks a cast make[3]: *** [sql_lex.o] Error 1 Platform specs: [esm@pritchel /usr/local/src/mysql-3.23.39]$ uname -a SunOS pritchel 5.7 Generic_106541-08 sun4u sparc SUNW,Ultra-5_10 [esm@pritchel /usr/local/src/mysql-3.23.39]$ which gcc /usr/local/bin/gcc [esm@pritchel /usr/local/src/mysql-3.23.39]$ ldd /usr/local/bin/gcc libc.so.1 => /usr/lib/libc.so.1 libdl.so.1 =>/usr/lib/libdl.so.1 /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1 [esm@pritchel /usr/local/src/mysql-3.23.39]$ gcc -v Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.7/2.95.3/specs gcc version 2.95.3 20010315 (release) [esm@pritchel /usr/local/src/mysql-3.23.39]$ pkginfo |grep -v SUNW application GNUautoc autoconf application GNUbashbash application SMCautom automake application SMCbison bison application SMCflexflex application SMCgcc gcc application SMCgzipgzip application SMCtar tar application SMCtop top [esm@pritchel /usr/local/src/mysql-3.23.39]$ env PWD=/usr/local/src/mysql-3.23.39 TZ=US/Eastern HOSTNAME=pritchel LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:/usr/share/lib MANPATH=/usr/local/man:/usr/local/ssl/man:/usr/bin/man:/usr/dt/share/man:/usr/dt/man:/usr/share/man:/usr/man PS1=[\u@\h \w]\$ USER=esm MACHTYPE=sparc-sun-solaris2.7 MAIL=/var/mail//esm OLDPWD=/usr/local/src LOGNAME=esm SHLVL=1 SHELL=/bin/sh HOSTTYPE=sparc OSTYPE=solaris2.7 HOME=/home/esm TERM=vt100 PATH=/usr/sbin:/sbin:/usr/local/bin:/usr/ccs/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/sbin:/usr/ccs/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin SSH_TTY=/dev/pts/14 _=/usr/bin/env >How-To-Repeat: make distclean ; CC=gcc CFLAGS="-O3" \ CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" \ ./configure --prefix=/usr/local/mysql --with-low-memory --enable-assembler >Fix: >Submitter-Id: >Originator:Elliot Metsger >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: Compile failure gcc 2.95.2 and 2.95.3 on sql_lex.cc: In function `void >lex_init()': >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-3.23.39 (Source distribution) >Environment: System: SunOS pritchel 5.7 Generic_106541-08 sun4u sparc SUNW,Ultra-5_10 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.7/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1690732 Oct 14 1999 /lib/libc.a lrwxrwxrwx 1 root root 11 Apr 19 10:35 /lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 bin bin 1109336 Oct 14 1999 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1690732 Oct 14 1999 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Apr 19 10:35 /usr/lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 bin bin 11
Re: Using the IN function
I've had the same problem once. Reading the manual I found out that MySQL is not SQL conform and does not support sub-selects. You'll have to rewrite the statement that you don't need a sub-select or if not possible create a temporary table. Regards, Robert - Original Message - From: "Bobby Chopra" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, April 26, 2001 2:09 PM Subject: Using the IN function > Probably a simple question: > > I am returning back to SQL after a break, and I was sure that I could do the > following when using the IN function: > > SELECT field1 FROM table1 where field1 NOT IN (SELECT DISTINCT field2 FROM > table2); > > but clearly not. > > Could anyone suggest what I am doing wrong, or is this type of statement not > valid syntax. > > Cheers > > Bobby Chopra > Applications Developer > ::Source New Media:: > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using the IN function
As I understand it, you can only list distinct values in an IN clause - MySQL doesn't yet support subqueries here - according to the manual.See page 139 of the pdf manual. -Original Message- From: Bobby Chopra [mailto:[EMAIL PROTECTED]] Sent: Thu, 26 April 2001 14:10 To: [EMAIL PROTECTED] Subject: Using the IN function Probably a simple question: I am returning back to SQL after a break, and I was sure that I could do the following when using the IN function: SELECT field1 FROM table1 where field1 NOT IN (SELECT DISTINCT field2 FROM table2); but clearly not. Could anyone suggest what I am doing wrong, or is this type of statement not valid syntax. Cheers Bobby Chopra Applications Developer ::Source New Media:: - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using the IN function
Probably a simple question: I am returning back to SQL after a break, and I was sure that I could do the following when using the IN function: SELECT field1 FROM table1 where field1 NOT IN (SELECT DISTINCT field2 FROM table2); but clearly not. Could anyone suggest what I am doing wrong, or is this type of statement not valid syntax. Cheers Bobby Chopra Applications Developer ::Source New Media:: - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php