MySQL Forums
Forum List  »  Performance

Re: Query not using index
Posted by: Chris O'Brien
Date: May 28, 2009 07:25AM

Hi, thanks for the response. Here is everything you asked for. I am running 5.0.70 built from Gentoo Portage.

You mentioned something about RAM. This is a one off thing I am doing to gather data, so I am actually doing it on my laptop which is a Dell E6400. It has a Core 2 Duo T9600 2.8Ghz processor with 4G of RAM so it's not exactly a slouch, but I also havn't tweaked the my.cnf file either. Were you thinking I don't have enough RAM set aside for all the keys to live in memory?

SHOW CREATE TABLE flows;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| flows | CREATE TABLE `flows` (
  `s_time` datetime default NULL,
  `e_time` datetime default NULL,
  `duration` float(8,3) default NULL,
  `proto` varchar(4) default NULL,
  `src_ip` varchar(15) default NULL,
  `src_port` varchar(5) default NULL,
  `dst_ip` varchar(15) default NULL,
  `dst_port` varchar(5) default NULL,
  `bytes` int(10) default NULL,
  `idx` int(8) NOT NULL auto_increment,
  PRIMARY KEY  (`idx`),
  KEY `stime` (`s_time`),
  KEY `etime` (`e_time`)
) ENGINE=MyISAM AUTO_INCREMENT=28601713 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SHOW TABLE STATUS LIKE 'flows';
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| flows | MyISAM |      10 | Dynamic    | 28601712 |             77 |  2203861036 | 281474976710655 |   1173725184 |         0 |       28601713 | 2009-05-26 14:43:04 | 2009-05-26 14:47:42 | 2009-05-26 14:53:05 | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

SHOW VARIABLES LIKE 'key%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| key_buffer_size          | 16777216 |
| key_cache_age_threshold  | 300      |
| key_cache_block_size     | 1024     |
| key_cache_division_limit | 100      |
+--------------------------+----------+

SHOW VARIABLES LIKE 'innodb%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| innodb_additional_mem_pool_size | 2097152                         |
| innodb_autoextend_increment     | 8                               |
| innodb_buffer_pool_awe_mem_mb   | 0                               |
| innodb_buffer_pool_size         | 16777216                        |
| innodb_checksums                | ON                              |
| innodb_commit_concurrency       | 0                               |
| innodb_concurrency_tickets      | 500                             |
| innodb_data_file_path           | ibdata1:10M:autoextend:max:128M |
| innodb_data_home_dir            |                                 |
| innodb_adaptive_hash_index      | ON                              |
| innodb_doublewrite              | ON                              |
| innodb_fast_shutdown            | 1                               |
| innodb_file_io_threads          | 4                               |
| innodb_file_per_table           | ON                              |
| 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          | 8388608                         |
| 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_rollback_on_timeout      | OFF                             |
| innodb_support_xa               | ON                              |
| innodb_sync_spin_loops          | 20                              |
| innodb_table_locks              | ON                              |
| innodb_thread_concurrency       | 8                               |
| innodb_thread_sleep_delay       | 10000                           |
+---------------------------------+---------------------------------+

I tried your two queries. The first on didn't run any faster. The second one didn't run properly and gave an error near 'inner'.

I am indeed mostly interested in minute by minute data but I don't think a summary table would help much. These are organic connections I am querying against, so they have all different lengths and atypical start and end times. I am basically checking each row to see if some part of it happened during some portion of each minute interval over the day.

Any more suggestions would be appreciated

Options: ReplyQuote


Subject
Views
Written By
Posted
4832
May 27, 2009 10:57AM
2124
May 27, 2009 08:28PM
Re: Query not using index
2036
May 28, 2009 07:25AM
1959
May 29, 2009 12:32AM
1484
June 04, 2009 12:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.