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 |
| wait_timeout | 28800 |
+----------------------------+---------------------------------------------+
36 rows in set (0.01 sec)
Lastly here is the show status:
mysql> show status;
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 1 |
| Aborted_connects | 3 |
| Created_tmp_tables | 7 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 7152 |
| Handler_read_first | 1508 |
| Handler_read_key | 384516 |
| Handler_read_next | 14657717 |
| Handler_read_rnd | 2022917 |
| Handler_update | 120668 |
| Handler_write | 184542 |
| Key_blocks_used | 29274 |
| Key_read_requests | 3203851 |
| Key_reads | 3535 |
| Key_write_requests | 281981 |
| Key_writes | 269061 |
| Max_used_connections | 46 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 77 |
| Open_files | 108 |
| Open_streams | 0 |
| Opened_tables | 176 |
| Questions | 414303 |
| Running_threads | 7 |
| Slow_queries | 318 |
| Uptime | 869733 |
+--------------------------+----------+
30 rows in set (0.00 sec)
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
