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_bu
RE: Efficient Select
So that looks like it does a select for all in table2 who DO have dedicated and just excludes those matches... thats an interesting way of doing it.. but that would still return a row for the other groups that the username belongs to in table2 right? so for instance if there is 2 entries in table 2 : id UserName Group 1 test Dedicated 2 test Limit1 Then it would match the second one, therefore the id that matched that username in table1 would be selected, or am i looking at it incorrectly.. Basically, I dont want any rows selected from table one if the username has an entry in table2 with a group of Dedicated.. I just ran that query on our database too and it appears to be stuck :\ On 02 Feb 2001 16:26:41 -0500, Johnson, Gregert wrote: > SELECT t1.id FROM table 1 t1 > LEFT JOIN table2 t2 ON t1.UserName = t2.UserName >AND t2.Group = 'Dedicated' >AND t1.StopTime = 0 > WHERE t2.id IS NULL; > > --Greg Johnson > > -Original Message- > From: Drew [mailto:[EMAIL PROTECTED]] > Sent: Friday, February 02, 2001 4:00 PM > To: [EMAIL PROTECTED] > Subject:Efficient Select > > Hi I was wondering if anyone could help me find the most efficient.. or > at least A efficient way to preform the following task: > > I have a table that has some data, one of which is a UserName column. >I > have another table that has multiple rows for that UserName. So table > one looks like: > > id int > UserName varchar > StopTimedatetime > > and table two looks like > > id int > UserName varchar > Group varchar > > So far I have > > SELECT table1.id > FROM table1, table2 > WHEREtable1.StopTime = 0 AND > table1.UserName = table2.username AND > table2.Group != 'Dedicated' > > You can see that I am trying to get the id's from the first table where > the stoptime is 0 and they do NOT have an entry in table2 that says > dedicated. There are multiple entries is table2 for each username... > Any suggestions? > > Thanks > Drew > > > - > 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 - 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
