Upgrading Mysql from 3.22 to 3.23
Hi there, Looking at upgrading from version 3.22.27 to the current release of 3.23 on Solaris 2.7 platform. We successfully built from built the 3.22.27 and looking to do the same with 3.23. Are there any gottcha's out there that I should be aware of? I looked at the Mysql documentation and see in the upgrade information on what changes there are in the new server ( http://www.mysql.com/doc/U/p/Upgrading-from-3.22.html) . I guess what I'm looking for is the upgrade instructions on how to do the upgrade (building compiling, and installing) a step by step if there is one over an existing Mysql install? Or does the install process just overwrite the old Mysql version if installed in same directory? If you need more detail let me know, Thanks in advance, Pat Pat Militzer Tech Support Supervisor Metro/MLS Inc. 11430 W North Ave Wauwatosa, WI 53226 414-778-5400 ext. 124 Fax 778-6143 email: [EMAIL PROTECTED] web site: www.metromls.com www.wihomes.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
Help Slow Query
Hi there, I have a query that is taking 19 - 23 sec's to complete, it used to take 5 sec or less to complete, the only thing we have added are a new database unrelated to this on. The system is a Sun Ultra 10s running Solaris 2.7 with 256mb and 333mhz processor. Mysql version is 3.22.27. Any Idea's, suggestions would be helpful. Here is the query in question: SELECT mlsnumber,streetnumber,streetdirect,FORMAT(currentprice,0), streetnam,unitnumber,municname,state,zipcd,salesassoc, listagentname,FORMAT(numrooms,0),FORMAT(numbedrooms,0),fullbaths FROM custmls where listingoffice = 0251 AND (listingstatus = 'ACT' or listingstatus = 'A*') AND scategory = 1 order by currentprice asc Here is the Explain for the query: +-+---+---+--+-+--+---+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+--+-+--+---+---+ | custmls | range | key1 | key1 |NULL | NULL | 20737 | | +-+---+---+--+-+--+---+---+ 1 row in set (0.02 sec) Also here is the indexes for the table: mysql show index from custmls; +-++---+--+---+---+- --+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality |sub_part | +-++---+--+---+---+- ---+--+ | custmls | 0 | PRIMARY | 1 | mlsnumber | A | 85045 | NULL | | custmls | 1 | agentcode | 1 | salesassoc| A | NULL | NULL | | custmls | 1 | key1 | 1 | listingstatus | A | NULL | NULL | | custmls | 1 | keyprice | 1 | currentprice | A | NULL | NULL | +-++---+--+---+---+- ---+--+ 4 rows in set (0.02 sec) Here is the variables for the system: mysql show variables; ++-+ | Variable_name | Value | ++-+ | back_log | 5 | | connect_timeout| 5 | | basedir| /export/home/usr/mysql/ | | datadir| /export/home/usr/mysql/var/ | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | join_buffer| 131072 | | flush_time | 0 | | key_buffer | 33550336| | language | /export/home/usr/mysql/share/mysql/english/ | | log| OFF | | log_update | OFF | | long_query_time| 10 | | low_priority_updates | OFF | | max_allowed_packet | 1048576 | | max_connections| 100 | | max_connect_errors | 10 | | max_delayed_insert_threads | 20 | | max_join_size | 4294967295 | | max_sort_length| 1024| | net_buffer_length | 16384 | | pid_file | /export/home/usr/mysql/var/mail.pid | | port | 3306| | protocol_version | 10 | | record_buffer | 1044480 | | skip_locking | OFF | | skip_networking| OFF | | socket | /tmp/mysql.sock | | sort_buffer| 4194296 | | table_cache| 512 | | thread_stack | 65536 | | tmp_table_size | 1048576 | | tmpdir | /var/tmp/ | | version| 3.22.27
Slow Loading page and query
Hi there, Using MySQL 3.22.27 and php3. The page I have does 7 queries to the database and then displays the page. The first time it runs it takes 9 secs or more before the data is displayed and then after that the thread is cached on MySQL. But when I run the same Query from MySQL console I get query times of .16 secs. I have changed the query and use the 'limit 2' in the sql statement and it still takes 9 or more secs if it is not a cached thread. The SQL statement is as follows: SELECT mlsnumber from custmls where listingoffice = 0251 and (listingstatus = 'ACT' or listingstatus = 'A*') and scategory = 1 limit 2 The only thing different in each query is the value of scategory. This the first time its run takes 9 or more seconds, there is an index on listingstatus the looks at 12000 records. I have tried an index on listing office,listingsstatus and scategory to see if it speeds up the query and I get a result of 0 , where there are 81 records matching when I did a count before the index was created. There are only 71685 records in the table. I have set the following variables: set-variable =key_buffer=16M set-variable =table_cache=256 set-variable =sort_buffer=4M set-variable =record_buffer=1M I'm running on a Sun Ultra 10s 333 sparc Processor and 256mb of ram, Solaris 7 OS. Any ideas would be great. Thanks in advance. Pat Pat Militzer Tech Support Supervisor Metro/MLS Inc. 11430 W North Ave Wauwatosa, WI 53226 414-778-5400 ext. 124 Fax 778-6143 email: [EMAIL PROTECTED] web site: www.metromls.com www.wihomes.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
how force mysql to use certain indexes
Hi there, I'm using mysql 3.22.27. How can I force mysql to use index's in my select statement? When I use explain to see what the optimiser is doing I see that its using the wrong index. When I try to use the USE INDEX(KEY1,KEY) i get a syntax error. Any ideas Thanks, Pat Pat Militzer Tech Support Supervisor Metro/MLS Inc. 11430 W North Ave Wauwatosa, WI 53226 414-778-5400 ext. 124 Fax 778-6143 email: [EMAIL PROTECTED] web site: www.metromls.com www.wihomes.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
SQL Syntax question
Hi there, I'm using mysql 3.22.27 and get error when trying to run this select statement: SELECT custmls.mlsnumber,custmls.streetnumber,custmls.streetdirect, FORMAT(custmls.currentprice,0),custmls.streetnam,custmls.streetaddtl, custmls.municname,custmls.state,custmls.zipcd,custmls.salesassoc, ipix.url,custmls.listagentname,FORMAT(custmls.numrooms,0), FORMAT(custmls.numbedrooms,0),custmls.fullbaths FROM custmls,ipix where custmls.mlsnumber = ipix.mlsnumber AND custmls.listingoffice = 0251 AND (custmls.listingstatus = 'ACT' or custmls.listingstatus = 'A*') AND custmls.scategory = 1 ORDER BY custmls.currentprice The error is: "1064 You have an error in your SQL syntax near 'ON ipix custmls.mlsnumber = ipix.mlsnumber where custmls.listingoffice = 0251 AN' at line 1 " Any ideas? Thanks Pat Pat Militzer Tech Support Supervisor Metro/MLS Inc. 11430 W North Ave Wauwatosa, WI 53226 414-778-5400 ext. 124 Fax 778-6143 email: [EMAIL PROTECTED] web site: www.metromls.com www.wihomes.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
