Re:Speed Up Query
Hi All, Following query take 25 minutes time,in this table having 3 core records,how to speed up this query,please help me.thanks advance SELECT b.circle_name, COUNT(a.mob), a.pack_price, DATE(a.req_date) FROM user_info_table a, circle_info_table b WHERE a.status = 'SUCCESS' AND a.sub_type IN( 'SUB', 'RESUB' ) AND Substring(a.mob, 1, 4) = b.mob_series AND DATE(a.req_date) = '2010-11-09' AND a.pack_id IN ( '206' ) AND mob NOT IN (SELECT mob FROM demo_user_info_table) GROUP BY a.pack_price, b.circle_name, DATE(a.req_date);
Re: Speed Up Query
using Execution plan: ++++-+---++-+---+-+- ---+ | id | select_type| table | type| possible_keys | key| key_len | ref | rows| Extra | ++++-+---++-+---+-+- ---+ | 1 | PRIMARY| b | ALL | NULL | NULL | NULL| NULL | 273 | Using temporary; Using f ilesort| | 1 | PRIMARY| a | ref | pack_id| pack_id | 5 | const | 1307430 | Using where | | 2 | DEPENDENT SUBQUERY | demo_users_info_table | unique_subquery | PRIMARY | PRIMARY| 130 | func | 1 | Using index; Full scan o n NULL key | ++++-+---++-+---+-+- ---+ On Fri, Nov 19, 2010 at 2:22 PM, Elizabeth Mattijsen l...@dijkmat.nl wrote: 1. use EXPLAIN to find out what the execution plan is. 2. from experience: don't use sub SELECTs (NOT IN (SELECT mob FROM demo_user_info_table)) if you want performance 3. from experience: don't use NOT IN (), but use IN () if you want performance On Nov 19, 2010, at 9:46 AM, kranthi kiran wrote: Hi All, Following query take 25 minutes time,in this table having 3 core records,how to speed up this query,please help me.thanks advance SELECT b.circle_name, COUNT(a.mob), a.pack_price, DATE(a.req_date) FROM user_info_table a, circle_info_table b WHERE a.status = 'SUCCESS' AND a.sub_type IN( 'SUB', 'RESUB' ) AND Substring(a.mob, 1, 4) = b.mob_series AND DATE(a.req_date) = '2010-11-09' AND a.pack_id IN ( '206' ) AND mob NOT IN (SELECT mob FROM demo_user_info_table) GROUP BY a.pack_price, b.circle_name, DATE(a.req_date);
need Help - Mysqldump issue
Hello, Starting today, my daily database backup script does not work :-( with mysqldump typically Out of memory error. So, I try to change max_allowed_packet option value, but I don't succeed (mysql run on ubuntu 10.04 OS with 5.1.41-3ubuntu12.7 0 mysql version.) On the last successfull backup, database size was : 2,59 Go Here is some of my test : (with --max_allowed_packet option = 512M ) /usr/bin/mysqldump -A --max_allowed_packet=512M --default-character-set=UTF8 -u root -p /tmp/testbackup01.sql Enter password: mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `jahia_sl2_version_content` at row: 0 (with --max_allowed_packet = 1024M or 2048 option : ) /usr/bin/mysqldump -A --max_allowed_packet=1024M --default-character-set=UTF8 -u root -p /tmp/testbackup01.sql Enter password: mysqldump: Out of memory (Needed 1405796107 bytes) mysqldump: Couldn't allocate memory (with --max_allowed_packet = 4096M option : ) /usr/bin/mysqldump -A --skip-quick --max_allowed_packet=4096M --default-character-set=UTF8 -u root -p /tmp/testbackup01.sql Warning: option 'max_allowed_packet': unsigned value 4294967296 adjusted to 2147483648 Enter password: mysqldump: Out of memory (Needed 702898104 bytes) mysqldump: Got error: 2008: MySQL client ran out of memory when retrieving data from server Adding --skip-opt --quick option does not solve the issue Adding --skip-quick, the error is : mysqldump: Out of memory (Needed 702898104 bytes) mysqldump: Got error: 2008: MySQL client ran out of memory when retrieving data from server Here is my config : /etc/mysql/my.cnf [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking bind-address= 127.0.0.1 key_buffer = 16M max_allowed_packet = 4096M thread_stack= 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size= 16M log_error= /var/log/mysql/error.log expire_logs_days= 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M includedir /etc/mysql/conf.d/ Any help would be appreciate !! Bests -- Christophe Dumonet Centre de Ressources Informatiques Institut Francais de Mecanique Avancee (IFMA) Campus des Cezeaux BP 265 63175 AUBIERE Cedex Tel : +33 - 4.73.28.80.64 Fax : +33 - 4.73.28.81.00 Mail : christophe.dumo...@ifma.fr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Speed Up Query
AND Substring(a.mob, 1, 4) = b.mob_series There's what is probably the major problem with your query: your join condition. Indices (you *do* have them on your join fields, don't you ?) only work on the entire field you've indexed. Function indices are not supported in MySQL, so you'll have to add a field that contains the correct substring. You can auto-fill that using a trigger. Another - more cosmetic - change I'd recommend is using join syntax instead of putting your join condition in the where clause. It may be mostly equivalent today, but that way you clearly indicate to the parse what he's supposed to join on. Statements also become a lot easier to read :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
