Re: Query Optimization
SELECT * FROM XYX WHERE VAL IN ('BLUE','RED','GREEN'); query will make it
faster, if the field is ENUM
On Fri, Nov 16, 2012 at 12:36 AM, Anupam Karmarkar
sb_akarmar...@yahoo.comwrote:
Hi All,
Consider a scenario, I have table XYZ which contains value follow
BLUE
RED
GREEN
NULL
following are queries we can use get this values
1. SELECT * FROM XYX WHERE VAL IN ('BLUE','RED','GREEN');
2. SELECT * FROM XYZ WHERE VAL IS NOT NULL
3. SELECT * FROM XYZ WHERE VAL = 'BLUE' OR VAL='RED' OR VAL='GREEN'
and more
So which one is good in terms of optimization. I guess, 1 and 3 are
similar in term of formation.
--Anupam
--
Thanks Regards,
P.Benaya Paul
http://www.codeasearch.com
http://www.iwannasearch.com
installing mysql from src
I have followed the steps from bellow to install mysql from src on my linux ( red hat 6 ) : shell groupadd mysql shell useradd -r -g mysql mysql # Beginning of source-build specific instructions shell tar zxvf mysql-VERSION.tar.gz shell cd mysql-VERSION shell cmake . shell make shell make install all these as root the point is that it did not install into /usr/local but into /usr/share these worked fine : # Postinstallation setup shell cd /usr/local/mysql shell chown -R mysql . shell chgrp -R mysql . but I can not create the DB or start the server : 1. [root@ericomtx bin]# pwd /usr/bin [root@ericomtx bin]# ls -la mysql* -rwxr-xr-x. 1 root root 4984411 Nov 15 13:26 mysql -rwxr-xr-x. 1 root root 110776 Nov 15 13:24 mysqlaccess -rwxr-xr-x. 1 root root1702 Apr 9 2010 mysqlaccess.conf -rwxr-xr-x. 1 root root 4290406 Nov 15 13:26 mysqladmin -rwxr-xr-x. 1 root root 4841390 Nov 15 13:26 mysqlbinlog -rwxr-xr-x. 1 root root 10349 Nov 15 13:24 mysqlbug -rwxr-xr-x. 1 root root 4281034 Nov 15 13:26 mysqlcheck -rwxr-xr-x. 1 root root 4994249 Nov 15 13:26 mysql_client_test -rwxr-xr-x. 1 root root6456 Nov 15 13:24 mysql_config -rwxr-xr-x. 1 root root4169 Nov 15 13:24 mysql_convert_table_format -rwxr-xr-x. 1 root root 22389 Nov 15 13:24 mysqld_multi -rwxr-xr-x. 1 root root 21432 Nov 15 13:24 mysqld_safe -rwxr-xr-x. 1 root root 4473050 Nov 15 13:26 mysqldump -rwxr-xr-x. 1 root root6602 Nov 15 13:24 mysqldumpslow -rwxr-xr-x. 1 root root3245 Nov 15 13:24 mysql_find_rows -rwxr-xr-x. 1 root root 483 Nov 15 13:24 mysql_fix_extensions -rwxr-xr-x. 1 root root 32209 Nov 15 13:24 mysqlhotcopy -rwxr-xr-x. 1 root root 4287853 Nov 15 13:26 mysqlimport -rwxr-xr-x. 1 root root 14472 Nov 15 13:24 mysql_install_db -rwxr-xr-x. 1 root root7694 Nov 15 13:24 mysql_secure_installation -rwxr-xr-x. 1 root root 16689 Nov 15 13:24 mysql_setpermission -rwxr-xr-x. 1 root root 4278732 Nov 15 13:26 mysqlshow -rwxr-xr-x. 1 root root 4331143 Nov 15 13:26 mysqlslap -rwxr-xr-x. 1 root root 4845542 Nov 15 13:26 mysqltest -rwxr-xr-x. 1 root root 729588 Nov 15 13:26 mysql_tzinfo_to_sql -rwxr-xr-x. 1 root root 1017021 Nov 15 13:26 mysql_upgrade -rwxr-xr-x. 1 root root 888746 Nov 15 13:26 mysql_waitpid -rwxr-xr-x. 1 root root3818 Nov 15 13:24 mysql_zap [root@ericomtx bin]# ./mysql_install_db --user =mysql 121116 14:50:18 [ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50080, now running 50504. Please use mysql_upgrade to fix this error. ERROR: 1558 Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50080, now running 50504. Please use mysql_upgrade to fix this error. 121116 14:50:18 [ERROR] Aborting but I CAN NOT update MySQL ... it doesn allow me to : [root@ericomtx bin]# ./mysql_upgrade Looking for 'mysql' as: ./mysql Looking for 'mysqlcheck' as: ./mysqlcheck Running 'mysqlcheck with default connection arguments ./mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed [root@ericomtx bin]# also ... you may say to me so I need to create the DB from my install dir ... ok : [root@ericomtx mysql-5.5.4-m3]# scripts/mysql_install_db --user=mysql WARNING: The host '' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing MySQL system tables... 121116 14:53:47 [Note] Buffered information: Performance schema disabled (reason: start parameters). 121116 14:53:47 [ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50080, now running 50504. Please use mysql_upgrade to fix this error. ERROR: 1558 Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50080, now running 50504. Please use mysql_upgrade to fix this error. 121116 14:53:47 [ERROR] Aborting 121116 14:53:47 [Note] /usr/sbin/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can try to start the mysqld daemon with: shell /usr/sbin/mysqld --skip-grant and use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. Please consult the MySQL manual section 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source are the MySQL email archives available at http://lists.mysql.com/. Please check all of the above before mailing us! And remember, if you do
Re: installing mysql from src
Am 16.11.2012 19:21, schrieb Érico: I have followed the steps from bellow to install mysql from src on my linux ( red hat 6 ) : shell groupadd mysql shell useradd -r -g mysql mysql # Beginning of source-build specific instructions shell tar zxvf mysql-VERSION.tar.gz shell cd mysql-VERSION shell cmake . shell make shell make install all these as root the point is that it did not install into /usr/local but into /usr/share first: why do you not take the src.rpm and modify the SPEC-file to install in /usr/local and build a RPM with a restricted user? building software directly and especially as root leads sooner or later in all kind of troubles because there will be orphaned files over the long and if oyu make any mistake you spit files all over the system while rpmbuild is chained and restricted if it doe snot run as root and makes sure you have each time you start the build a clean buildroot yes, learn to use rpmbuild takes a little time but after have it running you save much more and with rpmbuild -bs mysql.spec you can build your src.rpm to backup and re-use it on any machine 121116 14:50:18 [ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50080, now running 50504. Please use mysql_upgrade to fix this error clear after upgrade to another major version ERROR: 1558 Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50080, now running 50504. Please use mysql_upgrade to fix this error. 121116 14:50:18 [ERROR] Aborting but I CAN NOT update MySQL ... it doesn allow me to : [root@ericomtx bin]# ./mysql_upgrade Looking for 'mysql' as: ./mysql Looking for 'mysqlcheck' as: ./mysqlcheck Running 'mysqlcheck with default connection arguments ./mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect well, configure your client and server correctly to the same socket fileor use at least the command-line option --socket= mysql_upgrade --help is your friend _ /etc/my.cnf: [mysqld] socket = /var/lib/mysql/mysql.sock [client] socket = /var/lib/mysql/mysql.sock [mysqladmin] socket= /var/lib/mysql/mysql.sock [mysqld_safe] socket = /var/lib/mysql/mysql.sock signature.asc Description: OpenPGP digital signature
RE: Query Optimization
It depends on the distribution of the 4 'values' in that field.
If the cardinality is poor, then INDEX(VAL) won't be used, and they will all do
a table scan.
-Original Message-
From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
Sent: Friday, November 16, 2012 12:36 AM
To: mysql@lists.mysql.com
Subject: Query Optimization
Hi All,
Consider a scenario, I have table XYZ which contains value follow BLUE RED
GREEN NULL
following are queries we can use get this values
1. SELECT * FROM XYX WHERE VAL IN ('BLUE','RED','GREEN'); 2. SELECT * FROM
XYZ WHERE VAL IS NOT NULL 3. SELECT * FROM XYZ WHERE VAL = 'BLUE' OR
VAL='RED' OR VAL='GREEN'
and more
So which one is good in terms of optimization. I guess, 1 and 3 are similar
in term of formation.
--Anupam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
