Re: slower queries after partitioning

[Mat private]

Hi Rick,

thanks for your reply with the tips.
Apart from changing the types for the columns, do you know a way to make it a lot faster?

Are there other techniques besides normalization, type optimization and key optimization to make an inno db table faster?

these are the settings I have:
I run MySQL on a windows 2003 server (8 cores and 4 GB RAM) using the XAMPP package of apachefriends.org.
version 5.1.30-community
version_comment MySQL Community Server (GPL)
version_compile_machine ia32
version_compile_os Win32
Variable_name Value
have_innodb YES
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_size 8388608
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
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_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_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
Engine Support Comment Transactions XA Savepoints
MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO
InnoDB YES Supports transactions, row-level locking, and foreign keys YES YES YES
MyISAM DEFAULT Default engine as of MySQL 3.23 with great performance NO NO NO
BLACKHOLE YES /dev/null storage engine (anything you write to it disappears) NO NO NO
MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO
CSV YES CSV storage engine NO NO NO
ARCHIVE YES Archive storage engine NO NO NO


kind regards,

Matthijs