Indexing on a DATE field/bizarre speed issue with a LEFT JOIN?
Hi List, I sure do appreciate this list. I'm stumped on the following query: SELECT head.po FROM head LEFT JOIN line ON (head.sn=line.snHead) WHERE head.po 1 AND line.dateETA='2002-06-10' LIMIT 50 As is, this query is very fast (0.01 seconds when there are 25,000 records in 'head', and 50,000 records in 'line'). However, when I change the query to search on line.dateETA = or even = [somedate], the query takes a long time to return (2-3 seconds). The line.dateETA field is indexed. And, for both = and = queries, EXPLAIN returns the same information. The only thing I can think of is that NULL values are allowed in line.dateETA, and about half the values are NULL (22,000 lines). However, I tested changing the line.dateETA field to NOT NULL, and I didn't get any better performance. Is there a known speed issue in searching on date fields with = versus =? Thanks, Matt - 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: Indexing on a DATE field/bizarre speed issue with a LEFT JOIN?
Sorry for the long reply: At 02:57 PM 6/10/2002 -0500, you wrote: [snip] SELECT head.po FROM head LEFT JOIN line ON (head.sn=line.snHead) WHERE head.po 1 AND line.dateETA='2002-06-10' LIMIT 50 As is, this query is very fast (0.01 seconds when there are 25,000 records in 'head', and 50,000 records in 'line'). However, when I change the query to search on line.dateETA = or even = [somedate], the query takes a long time to return (2-3 seconds). The line.dateETA field is indexed. And, for both = and = queries, EXPLAIN returns the same information. [/snip] Since head.po is the return item here, indexing it will speed up the query. HTH! Jay sql, mysql, query head.po is already indexed (as are head.sn and line.snHead), but I'm not so sure that indexing the other fields is the issue since the query is very fast in the = case. Thanks, Matt - 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: Thread based optimization?
Hi All At 11:17 PM 6/6/2002 -0700, Jeremy wrote: However, there is the occasional select query that requires a good amount of work on the database. Why? Can it be optimized? The query is needs about 10 left joins, and requires data from about 12 different tables (some tables have 50,000 rows). I believe everything is indexed properly, because the query is very fast when it is run without the order by, and group by clauses... We are using LIMIT 50, but MySQL still has to sort through many (50,000) rows. Is there a smart way to limit system resources per thread? Nope. I saw section 4.6.3 of the maual (Limiting user resources), but was hoping for some kind of undocumented tweak. I'd much rather have the long-query person wait an extra few seconds than have several short-query people wait. I've tried tuning all the typical parameters, but I don't think the logjam is memory-based (the hard disk doesn't move). Hmm... Have you tried making the slow query a LOW_PRIORITY one? I don't think a select statements have a low_priority flag?? Is there an alternative? I'd rather not go back and change all the fast queries to high_priority, and I don't even think that will have much of an affect, since MySQL only queue's high_priority selects in front of inserts and updates. Thanks Jeremy, Matt - 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
Thread based optimization?
Dear list, After doing a good bit of reading, the details are still hazy. I have a web-based application (about 20 tables, with up to 100,000 records per table - but most tables are much smaller). 99% of select queries are very very fast. However, there is the occasional select query that requires a good amount of work on the database. It may take a few seconds to return. This is okay, however, these queries drastically affect the performance of other users. Is there a smart way to limit system resources per thread? I'd much rather have the long-query person wait an extra few seconds than have several short-query people wait. I've tried tuning all the typical parameters, but I don't think the logjam is memory-based (the hard disk doesn't move). The database server is Win2k, 512Mb memory, PIII @ 450mhz. Variables are as below. Thanks in advance, Matt back_log| 50 basedir | C:\mysql\ binlog_cache_size | 32768 character_set | latin1 character_sets | concurrent_insert | ON connect_timeout | 5 datadir | C:\mysql\data\ delay_key_write | ON delayed_insert_limit| 100 delayed_insert_timeout | 300 delayed_queue_size | 1000 flush | OFF flush_time | 0 have_bdb| NO have_gemini | NO have_innodb | NO have_isam | YES have_raid | NO have_openssl| NO init_file | interactive_timeout | 28800 join_buffer_size| 131072 key_buffer_size | 67104768 language| C:\mysql\share\english\ large_files_support | ON log | OFF log_update | OFF log_bin | ON log_slave_updates | OFF log_long_queries| ON long_query_time | 1 low_priority_updates| OFF lower_case_table_names | 1 max_allowed_packet | 1047552 max_binlog_cache_size | 4294967295 max_binlog_size | 1073741824 max_connections | 100 max_connect_errors | 10 max_delayed_threads | 20 max_heap_table_size | 16777216 max_join_size | 4294967295 max_sort_length | 1024 max_user_connections| 0 max_tmp_tables | 32 max_write_lock_count| 4294967295 myisam_max_extra_sort_file_size | 256 myisam_max_sort_file_size | 2047 myisam_recover_options | 0 myisam_sort_buffer_size | 67108864 net_buffer_length | 16384 net_read_timeout| 30 net_retry_count | 10 net_write_timeout | 60 open_files_limit| 0 pid_file| C:\mysql\data\oceanbeach.pi port| 3306 protocol_version| 10 record_buffer | 1044480 record_rnd_buffer | 1044480 query_buffer_size | 0 safe_show_database | OFF server_id | 1 slave_net_timeout | 3600 skip_locking| ON skip_networking | OFF skip_show_database | OFF slow_launch_time| 2 socket | MySQL sort_buffer | 1048568 sql_mode| 0 table_cache | 100 table_type | MYISAM thread_cache_size | 8 thread_stack| 65536 transaction_isolation | READ-COMMITTED timezone| Pacific Daylight Time tmp_table_size | 33554432 tmpdir | C:\WINNT\TEMP\ version | 3.23.49-nt-log wait_timeout| 28800 - 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
