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]

Reply via email to