List:General Discussion« Previous MessageNext Message »
From:Mikhail Berman Date:November 18 2005 2:19pm
Subject:RE: A "key" question
View as plain text  
Dear Jeremy,

Thank you for your help.

I do have an exact situation you have assume I have. Here is the output
of SHOW CREATE TABLE

+---------------------------+-------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
---------------------------------------------------+
| Table                     | Create Table
|
+---------------------------+-------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
---------------------------------------------------+
| TICKER_HISTORY_PRICE_DATA | CREATE TABLE `TICKER_HISTORY_PRICE_DATA` (
  `price_data_ticker` char(8) NOT NULL default '',
  `price_data_date` date NOT NULL default '0000-00-00',
  `price_data_open` float default NULL,
  `price_data_high` float default NULL,
  `price_data_low` float default NULL,
  `price_data_close` float default NULL,
  `price_data_volume` float default NULL,
  UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------------------------+-------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
---------------------------------------------------+
1 row in set (0.01 sec)

As you can see, Unique KEY is on two first fields, but most of the work,
joins & searches, will be done on the second field "price_data_date".

Here is my SHOW VARIABLES

mysql> show variables;
+---------------------------------+-------------------------------------
---------------------------------------------------------------------+
| Variable_name                   | Value
|
+---------------------------------+-------------------------------------
---------------------------------------------------------------------+
| back_log                        | 50
|
| basedir                         |
/data/mysql/mysql-standard/reloc/mysql-standard-4.1.10a-sun-solaris2.9-s
parc-64bit/                      |
| binlog_cache_size               | 32768
|
| bulk_insert_buffer_size         | 8388608
|
| character_set_client            | latin1
|
| character_set_connection        | latin1
|
| character_set_database          | latin1
|
| character_set_results           | latin1
|
| character_set_server            | latin1
|
| character_set_system            | utf8
|
| character_sets_dir              |
/data/mysql/mysql-standard/reloc/mysql-standard-4.1.10a-sun-solaris2.9-s
parc-64bit/share/mysql/charsets/ |
| collation_connection            | latin1_swedish_ci
|
| collation_database              | latin1_swedish_ci
|
| collation_server                | latin1_swedish_ci
|
| concurrent_insert               | ON
|
| connect_timeout                 | 5
|
| datadir                         | /raid5/mysqldata/
|
| date_format                     | %Y-%m-%d
|
| datetime_format                 | %Y-%m-%d %H:%i:%s
|
| default_week_format             | 0
|
| delay_key_write                 | ON
|
| delayed_insert_limit            | 100
|
| delayed_insert_timeout          | 300
|
| delayed_queue_size              | 1000
|
| expire_logs_days                | 0
|
| flush                           | OFF
|
| flush_time                      | 0
|
| ft_boolean_syntax               | + -><()~*:""&|
|
| ft_max_word_len                 | 84
|
| ft_min_word_len                 | 4
|
| ft_query_expansion_limit        | 20
|
| ft_stopword_file                | (built-in)
|
| group_concat_max_len            | 1024
|
| have_archive                    | YES
|
| have_bdb                        | NO
|
| have_compress                   | YES
|
| have_crypt                      | YES
|
| have_csv                        | NO
|
| have_example_engine             | NO
|
| have_geometry                   | YES
|
| have_innodb                     | YES
|
| have_isam                       | NO
|
| have_ndbcluster                 | NO
|
| have_openssl                    | NO
|
| have_query_cache                | YES
|
| have_raid                       | NO
|
| have_rtree_keys                 | YES
|
| have_symlink                    | YES
|
| init_connect                    |
|
| init_file                       |
|
| init_slave                      |
|
| innodb_additional_mem_pool_size | 1048576
|
| innodb_autoextend_increment     | 8
|
| innodb_buffer_pool_awe_mem_mb   | 0
|
| innodb_buffer_pool_size         | 8388608
|
| innodb_data_file_path           | ibdata1:10M:autoextend
|
| innodb_data_home_dir            |
|
| innodb_fast_shutdown            | ON
|
| innodb_file_io_threads          | 4
|
| innodb_file_per_table           | OFF
|
| innodb_flush_log_at_trx_commit  | 1
|
| innodb_flush_method             |
|
| innodb_force_recovery           | 0
|
| innodb_lock_wait_timeout        | 50
|
| innodb_locks_unsafe_for_binlog  | OFF
|
| 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_max_dirty_pages_pct      | 90
|
| innodb_max_purge_lag            | 0
|
| innodb_mirrored_log_groups      | 1
|
| innodb_open_files               | 300
|
| innodb_table_locks              | ON
|
| innodb_thread_concurrency       | 8
|
| interactive_timeout             | 28800
|
| join_buffer_size                | 131072
|
| key_buffer_size                 | 402653184
|
| key_cache_age_threshold         | 300
|
| key_cache_block_size            | 1024
|
| key_cache_division_limit        | 100
|
| language                        |
/data/mysql/mysql-standard/reloc/mysql-standard-4.1.10a-sun-solaris2.9-s
parc-64bit/share/mysql/english/  |
| large_files_support             | ON
|
| license                         | GPL
|
| local_infile                    | ON
|
| locked_in_memory                | OFF
|
| log                             | OFF
|
| log_bin                         | ON
|
| log_error                       |
|
| log_slave_updates               | OFF
|
| log_slow_queries                | OFF
|
| log_update                      | OFF
|
| log_warnings                    | 1
|
| long_query_time                 | 10
|
| low_priority_updates            | OFF
|
| lower_case_file_system          | OFF
|
| lower_case_table_names          | 0
|
| max_allowed_packet              | 16776192
|
| max_binlog_cache_size           | 18446744073709551615
|
| max_binlog_size                 | 1073741824
|
| max_connect_errors              | 10
|
| max_connections                 | 100
|
| max_delayed_threads             | 20
|
| max_error_count                 | 64
|
| max_heap_table_size             | 16777216
|
| max_insert_delayed_threads      | 20
|
| max_join_size                   | 18446744073709551615
|
| max_length_for_sort_data        | 1024
|
| max_relay_log_size              | 0
|
| max_seeks_for_key               | 18446744073709551615
|
| max_sort_length                 | 1024
|
| max_tmp_tables                  | 32
|
| max_user_connections            | 0
|
| max_write_lock_count            | 18446744073709551615
|
| myisam_data_pointer_size        | 4
|
| myisam_max_extra_sort_file_size | 2147483648
|
| myisam_max_sort_file_size       | 9223372036854775807
|
| myisam_recover_options          | OFF
|
| myisam_repair_threads           | 1
|
| myisam_sort_buffer_size         | 67108864
|
| net_buffer_length               | 16384
|
| net_read_timeout                | 30
|
| net_retry_count                 | 10
|
| net_write_timeout               | 60
|
| new                             | OFF
|
| old_passwords                   | OFF
|
| open_files_limit                | 1134
|
| pid_file                        | /raid5/mysqldata/SB2000.pid
|
| port                            | 3306
|
| preload_buffer_size             | 32768
|
| protocol_version                | 10
|
| query_alloc_block_size          | 8192
|
| query_cache_limit               | 1048576
|
| query_cache_min_res_unit        | 4096
|
| query_cache_size                | 33554432
|
| query_cache_type                | ON
|
| query_cache_wlock_invalidate    | OFF
|
| query_prealloc_size             | 8192
|
| range_alloc_block_size          | 2048
|
| read_buffer_size                | 2093056
|
| read_only                       | OFF
|
| read_rnd_buffer_size            | 8384512
|
| relay_log_purge                 | ON
|
| rpl_recovery_rank               | 0
|
| secure_auth                     | OFF
|
| server_id                       | 1
|
| skip_external_locking           | ON
|
| skip_networking                 | OFF
|
| skip_show_database              | OFF
|
| slave_net_timeout               | 3600
|
| slow_launch_time                | 2
|
| socket                          | /tmp/mysql.sock
|
| sort_buffer_size                | 2097144
|
| sql_mode                        |
|
| storage_engine                  | MyISAM
|
| sync_binlog                     | 0
|
| sync_replication                | 0
|
| sync_replication_slave_id       | 0
|
| sync_replication_timeout        | 0
|
| sync_frm                        | ON
|
| system_time_zone                | EST
|
| table_cache                     | 512
|
| table_type                      | MyISAM
|
| thread_cache_size               | 8
|
| thread_concurrency              | 8
|
| thread_stack                    | 196608
|
| time_format                     | %H:%i:%s
|
| time_zone                       | SYSTEM
|
| tmp_table_size                  | 33554432
|
| tmpdir                          |
|
| transaction_alloc_block_size    | 8192
|
| transaction_prealloc_size       | 4096
|
| tx_isolation                    | REPEATABLE-READ
|
| version                         | 4.1.10a-standard-log
|
| version_comment                 | MySQL Community Edition - Standard
(GPL)                                                                 |
| version_compile_machine         | sparc
|
| version_compile_os              | sun-solaris2.9
|
| wait_timeout                    | 28800
|
+---------------------------------+-------------------------------------
---------------------------------------------------------------------+
180 rows in set (0.00 sec)

Mikhail Berman

-----Original Message-----
From: Jeremy Cole [mailto:jcole@stripped] 
Sent: Thursday, November 17, 2005 5:23 PM
To: Mikhail Berman
Cc: Jasper Bryant-Greene; mysql@stripped
Subject: Re: A "key" question

Hi Mikhail,

> I may not have been precise in my question, but the Unique Index in 
> question is a two fields index, and I was looking to find out wisdom 
> from the List if there is sense and/or experience in keying 
> second(left) field on in the Unique Index to speed up a search.

If you have a UNIQUE(a, b), then MySQL can use it as an index for (a),
or (a, b), but NOT for (b).  In this context, it won't help generally to
create an index on (a), but it may help to create one on (b) depending
on your queries.

> I am dealing with 32M rows table, where second field in the Unique 
> Index is a date field. Unfortunately for my simple "SELECT MAX(Date) 
> as Latest_Date from THE_TABLE" took 4 minutes and some seconds, so 
> before I will go and buy bigger server I needed to re-assure myself 
> that there is no other way.

Four minutes to find a MAX(date) is too long for any kind of hardware. 
It should be much faster.  Can you post the output of:

   * SHOW CREATE TABLE tbl
   * SHOW VARIABLES

FYI:

mysql> select max(dep_time) from ontime_all;
+---------------------+
| max(dep_time)       |
+---------------------+
| 2005-05-31 23:59:00 |
+---------------------+
1 row in set (49.76 sec)

mysql> select count(*) from ontime_all;
+----------+
| count(*) |
+----------+
| 33395077 |
+----------+
1 row in set (0.00 sec)

Could be a lot faster, even, but these are MERGE tables so it's really
65 tables that are being checked...


Regards,

Jeremy

-- 
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
Thread
A "key" questionMikhail Berman17 Nov
  • Re: A "key" questionJasper Bryant-Greene17 Nov
  • Re: A "key" questionPeter Brawley17 Nov
RE: A "key" questionMikhail Berman17 Nov
  • Re: A "key" questionJeremy Cole17 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionJeremy Cole18 Nov
  • Re: A "key" questionMichael Stassen18 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionMichael Stassen18 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionDavid Griffiths18 Nov
  • Connection ProblemTripp Bishop18 Nov
    • Re: Connection ProblemMichael Stassen18 Nov
      • Re: Connection ProblemTripp Bishop18 Nov