The fact that you are joining 10 tables together in one query may be a
major portion of your performance problem. Even if the *average* size of
each table is only 10 rows, the MySQL engine will have to pour over
10x10x10x10x10x10x10x10x10x10 = 10 to the 10th power = 1e10 =
10,000,000,000 (10 billion) combinations of data rows before it gets to
even think about some of your WHERE conditions.
I have a few suggestions:
FIRST - Rewrite your query to use the explicit INNER JOIN form of table
joins and NOT the comma-join form you are currently using. The explicit
form is much MUCH easier to double check that you have properly JOINed
each table to each other table. Without properly defined join conditions
you will create a Cartesian product between two or more of your tables.
Cartesian products are rarely good things for query performance.
SECOND - Check your indexes. Make sure that you are joining tables on
columns that appear first in at least one index. Carefully consider
creating a multiple column index or two (especially if this is a common
query and/or you use those conditions often).
THIRD - Consider evaluating this query in stages. Based on your re-write
to eliminate the comma-joins, I can help you with that, too.
FOURTH - Consider using table aliases. It may make your SELECT and WHERE
clauses easier to read.
I look forward to seeing the rewrite.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"DeRyl" <[EMAIL PROTECTED]> wrote on 09/23/2004 05:50:21 AM:
> 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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
