Why does 1st query take so long?
I've noticed that when developing queries involving complex joins on
tables I sometimes seem to get appallingly long times the first time I run
a query, but the second and subsequent time I run it - even substituting
a different value for a field value I'm matching on - the query runs
quickly.
Does mySQL do some behind-the-scenes reindexing the first time,
resulting in the speed differences I see? If so is there some way I can
force it to re-build indices periodically so I can ensure that queries are
generally fast? Is this the point I should be going out and buying a book
about mySQL? ;-)
Here'a an example query:
SELECT d.ifIndex, d.MAC, a.IPadd, d.nMACs, d.mtime, x.deviceID, x.ifIndex
FROM dot1d as d
LEFT JOIN MAC_connections as x
ON d.MAC = x.MAC
LEFT JOIN IP_MAC as a
ON a.MAC = d.MAC
WHERE x.deviceID=d.deviceID AND x.ifIndex=d.ifIndex AND
d.deviceID= {some value}
My MAC_Connections table is indexed on MAC, and IP_MAC is indexed on MAC.
mySQL Version is 3.22.32
regards,
--
John Stumbles [EMAIL PROTECTED]
I.T. Services Centre, University of Reading http://www.rdg.ac.uk/~visstmbl
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
never generalise
-
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: Why does 1st query take so long?
- Original Message - From: [EMAIL PROTECTED] To: "mySQL list" [EMAIL PROTECTED] Sent: Tuesday, January 16, 2001 3:57 PM Subject: Why does 1st query take so long? I've noticed that when developing queries involving complex joins on tables I sometimes seem to get appallingly long times the first time I run a query, but the second and subsequent time I run it - even substituting a different value for a field value I'm matching on - the query runs quickly. Does mySQL do some behind-the-scenes reindexing the first time, resulting in the speed differences I see? If so is there some way I can force it to re-build indices periodically so I can ensure that queries are generally fast? Is this the point I should be going out and buying a book about mySQL? ;-) I think its because the Operating System is caching the data the query are run on - 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
Why does query hang? [was: Why does 1st query take so long?
On Tue, 16 Jan 2001, Jeremy D. Zawodny wrote: On Tue, Jan 16, 2001 at 08:21:19PM +, John Stumbles wrote: I can't readily reproduce the first-time big difference scenario. OK, I can now: I have a table - MAC_connections:Data records:6689 +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | MAC | char(12) | | PRI | | | | deviceID | int(10) unsigned | YES | | NULL| | | ifIndex | int(10) unsigned | YES | | NULL| | | nMACs| int(10) unsigned | YES | | NULL| | | mtime| timestamp(14)| YES | | NULL| | +--+--+--+-+-+---+ SELECT * from MAC_connections or even SELECT count(*) from MAC_connections hangs! (it's been sitting there for the last 10 minutes and hasn't produced anything!) I guess it was working OK a few hours ago as I was working on a query involving a LEFT JOIN to this table: suddently it stopped working and (after hours of hair-tearing and head-banging :-) I eventually got right back to simple queries like the one above and found where the problem was. I can get data from other tables OK. isamchk -e MAC_connections doesn't complain: Checking ISAM file: MAC_connections Data records:6689 Deleted blocks: 0 - check file-size - check delete-chain - check index reference - check records and index references What's going on? Any ideas? show status shows: +--++ | Variable_name| Value | +--++ | Aborted_clients | 79 | | Aborted_connects | 3 | | Created_tmp_tables | 289| | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 56663884 | | Handler_read_first | 199| | Handler_read_key | 645684988 | | Handler_read_next| 581963979 | | Handler_read_rnd | 624846288 | | Handler_update | 321370818 | | Handler_write| 287600901 | | Key_blocks_used | 7822 | | Key_read_requests| 1654937380 | | Key_reads| 157365 | | Key_write_requests | 255616015 | | Key_writes | 137012352 | | Max_used_connections | 31 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 118| | Open_files | 101| | Open_streams | 0 | | Opened_tables| 1302 | | Questions| 540752383 | | Running_threads | 32 | | Slow_queries | 229871 | | Uptime | 7891238| +--++ show variables: ++-+ | Variable_name | Value | ++-+ | back_log | 5 | | connect_timeout| 5 | | basedir| /opt/local/ | | datadir| /DATA/mySQLdata/| | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | join_buffer| 131072 | | flush_time | 0 | | key_buffer | 8388600 | | language | /opt/local/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| | max_write_lock_count | 4294967295 | | net_buffer_length | 16384 | | pid_file | /usr/local/var/mysqld.pid | | port | 3306| | protocol_version | 10 | | record_buffer | 131072
Re: Why does query hang? [was: Why does 1st query take so long?
what does a 'show processlist' reveal? On 17 Jan 2001 00:08:23 +, John Stumbles wrote: On Tue, 16 Jan 2001, Jeremy D. Zawodny wrote: On Tue, Jan 16, 2001 at 08:21:19PM +, John Stumbles wrote: I can't readily reproduce the first-time big difference scenario. OK, I can now: I have a table - MAC_connections: Data records:6689 +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | MAC | char(12) | | PRI | | | | deviceID | int(10) unsigned | YES | | NULL| | | ifIndex | int(10) unsigned | YES | | NULL| | | nMACs| int(10) unsigned | YES | | NULL| | | mtime| timestamp(14)| YES | | NULL| | +--+--+--+-+-+---+ SELECT * from MAC_connections or even SELECT count(*) from MAC_connections hangs! (it's been sitting there for the last 10 minutes and hasn't produced anything!) I guess it was working OK a few hours ago as I was working on a query involving a LEFT JOIN to this table: suddently it stopped working and (after hours of hair-tearing and head-banging :-) I eventually got right back to simple queries like the one above and found where the problem was. I can get data from other tables OK. isamchk -e MAC_connections doesn't complain: Checking ISAM file: MAC_connections Data records:6689 Deleted blocks: 0 - check file-size - check delete-chain - check index reference - check records and index references What's going on? Any ideas? show status shows: +--++ | Variable_name| Value | +--++ | Aborted_clients | 79 | | Aborted_connects | 3 | | Created_tmp_tables | 289| | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 56663884 | | Handler_read_first | 199| | Handler_read_key | 645684988 | | Handler_read_next| 581963979 | | Handler_read_rnd | 624846288 | | Handler_update | 321370818 | | Handler_write| 287600901 | | Key_blocks_used | 7822 | | Key_read_requests| 1654937380 | | Key_reads| 157365 | | Key_write_requests | 255616015 | | Key_writes | 137012352 | | Max_used_connections | 31 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 118| | Open_files | 101| | Open_streams | 0 | | Opened_tables| 1302 | | Questions| 540752383 | | Running_threads | 32 | | Slow_queries | 229871 | | Uptime | 7891238| +--++ show variables: ++-+ | Variable_name | Value | ++-+ | back_log | 5 | | connect_timeout| 5 | | basedir| /opt/local/ | | datadir| /DATA/mySQLdata/| | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | join_buffer| 131072 | | flush_time | 0 | | key_buffer | 8388600 | | language | /opt/local/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| | max_write_lock_count | 4294967295 | | net_buffer_length | 16384 | | pid_file |
