This is probably due to having indexed only single columns. MySQL uses only one index per column, meaning if you have a where with two conditions you need a combined index for the columns used. If there are only indeces for the single columns, only one index for one condition will be used, the rest requires a full table scan (this is explained somewhere in the docs, have a look). Stefan
Am Thursday 23 September 2004 11:50 schrieb DeRyl: > hello, > > I have database with 30 tables [some have over 2000k some other over 4000k > rec] > > when I want to run a sql with a few conditions the answer is dramatically > slow [over 70 seconds!] > sql-s with one condition usually works well.. > > how is the corrcet way to optimize the database and sql questions? > the answer from sql should be under 1 second... > > SHOW VARIABLES gives these informations: > > Variable_name Value > back_log 50 > basedir /usr/local/mysql/ > bdb_cache_size 8388600 > bdb_log_buffer_size 32768 > bdb_home /dysk/mysql/data/ > bdb_max_lock 10000 > bdb_logdir > bdb_shared_data OFF > bdb_tmpdir /tmp/ > bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2... > binlog_cache_size 32768 > bulk_insert_buffer_size 8388608 > character_set latin2 > character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis... > concurrent_insert ON > connect_timeout 5 > convert_character_set > datadir /dysk/mysql/data/ > default_week_format 0 > delay_key_write ON > delayed_insert_limit 100 > delayed_insert_timeout 300 > delayed_queue_size 1000 > flush OFF > flush_time 0 > ft_boolean_syntax + -><()~*:""&| > ft_min_word_len 4 > ft_max_word_len 254 > ft_max_word_len_for_sort 20 > ft_stopword_file (built-in) > have_bdb YES > have_crypt YES > have_innodb YES > have_isam YES > have_raid YES > have_symlink YES > have_openssl NO > have_query_cache YES > init_file > innodb_additional_mem_pool_size 1048576 > innodb_buffer_pool_size 8388608 > innodb_data_file_path ibdata1:10M:autoextend > innodb_data_home_dir > innodb_file_io_threads 4 > innodb_force_recovery 0 > innodb_thread_concurrency 8 > innodb_flush_log_at_trx_commit 1 > innodb_fast_shutdown ON > innodb_flush_method > innodb_lock_wait_timeout 50 > innodb_log_arch_dir ./ > innodb_log_archive OFF > innodb_log_buffer_size 1048576 > innodb_log_file_size 5242880 > innodb_log_files_in_group 2 > innodb_log_group_home_dir ./ > innodb_mirrored_log_groups 1 > innodb_max_dirty_pages_pct 90 > interactive_timeout 28800 > join_buffer_size 131072 > key_buffer_size 8388600 > language /usr/local/mysql/share/mysql/polish/ > large_files_support ON > license GPL > local_infile ON > locked_in_memory OFF > log OFF > log_update OFF > log_bin OFF > log_slave_updates OFF > log_slow_queries OFF > log_warnings ON > long_query_time 10 > low_priority_updates OFF > lower_case_file_system OFF > lower_case_table_names 0 > max_allowed_packet 1048576 > max_binlog_cache_size 4294967295 > max_binlog_size 1073741824 > max_connections 100 > max_connect_errors 10 > max_delayed_threads 20 > max_insert_delayed_threads 20 > max_heap_table_size 16777216 > max_join_size 18446744073709551615 > max_relay_log_size 0 > max_seeks_for_key 4294967295 > max_sort_length 1024 > max_user_connections 0 > max_tmp_tables 32 > max_write_lock_count 4294967295 > myisam_max_extra_sort_file_size 268435456 > myisam_max_sort_file_size 2147483647 > myisam_repair_threads 1 > myisam_recover_options OFF > myisam_sort_buffer_size 8388608 > net_buffer_length 16384 > net_read_timeout 30 > net_retry_count 10 > net_write_timeout 60 > new OFF > open_files_limit 1024 > pid_file /dysk/mysql/data/mysqld.pid > log_error > port 3306 > protocol_version 10 > query_alloc_block_size 8192 > query_cache_limit 1048576 > query_cache_size 0 > query_cache_type ON > query_prealloc_size 8192 > range_alloc_block_size 2048 > read_buffer_size 131072 > read_only OFF > read_rnd_buffer_size 262144 > rpl_recovery_rank 0 > server_id 0 > slave_net_timeout 3600 > skip_external_locking ON > skip_networking OFF > skip_show_database OFF > slow_launch_time 2 > socket /tmp/mysql.sock > sort_buffer_size 2097144 > sql_mode 0 > table_cache 64 > table_type MYISAM > thread_cache_size 0 > thread_stack 196608 > tx_isolation REPEATABLE-READ > timezone CEST > tmp_table_size 33554432 > tmpdir /tmp/ > transaction_alloc_block_size 8192 > transaction_prealloc_size 4096 > version 4.0.20-max > version_comment Official MySQL-max binary > version_compile_os pc-linux > wait_timeout 28800 > > a good example of question is: > select distinct logo, klient.klientid, klientnazwa, struktura, > concat(kodpocztowy,' ',miejscowosc) miasto, aparatnumer, > concat(ulicaskrot,' ',ulicanazwa,' ',posesja) ulica,concat('woj. > ',wojewodztwo,' powiat: ',powiat) wojpow, klientbranza branza, email, www, > wizytowka > from klient, klientulice, klientulica, klientmiejscowosci, > klientmiejscowosc, branzaslowa, branzaslowo, klientbranza, klientslowa, > klientslowo > where wojewodztwoid=7 AND klientulica.klientulica like'dwo%' AND > klient.klientid = klientulice.klientid AND klientulice.klientulicaid= > klientulica.klientulicaid AND klientmiejscowosc.klientmiejscowosc > like'war%' AND klient.klientid = klientmiejscowosci.klientid AND > klientmiejscowosci.klientmiejscowoscid= > klientmiejscowosc.klientmiejscowoscid AND branzaslowo.branzaslowo > like'sam%' AND klient.klientid = klientbranza.klientid AND > klientbranza.branzaid=branzaslowa.branzaid AND branzaslowa.branzaslowoid= > branzaslowo.branzaslowoid AND (klientslowo.klientslowo LIKE 'sam%') AND > klient.klientid = klientslowa.klientid AND klientslowa.klientslowoid= > klientslowo.klientslowoid > > can anyone help me with this? > > with best regards > Darek -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]