Am Thursday 23 September 2004 13:22 schrieb Stefan Kuhn:
> 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
Sorry, this should read "one index per table"...
> 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
--
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]
