Hi!
On Nov 22, Dilipan Sebastiampillai wrote:
I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query using LEFT JOIN gives me different result.
The result from 4.0.18 does s real LEFT JOIN but 4.1.7 differs sometimes and I don't want that.
I think it comes from an 'optimization' .... how can I make a query without using the LEFT JOIN optimization of MySQL 4.1.7 ?
What is the query ? Can you provide a repeatable test case ?
Regards, Sergei
the answer is amazingly wrong ! have a look :
mysql> SELECT hosts.name,hosts.hostId, tries.hostId, tries.status FROM hosts LEFT JOIN tries ON tries.hostId=hosts.hostId AND tries.status IN('running','waitkill','preemption') LIMIT 20;
+---------+--------+--------+---------+
| name | hostId | hostId | status |
+---------+--------+--------+---------+
| chimp13 | 1530 | 1393 | running |
| chimp13 | 1530 | 1485 | running |
| chimp13 | 1530 | 1418 | running |
| chimp13 | 1530 | 1499 | running |
| chimp13 | 1530 | 1499 | running |
| chimp13 | 1530 | 1438 | running |
| chimp13 | 1530 | 1514 | running |
| chimp13 | 1530 | 1491 | running |
| chimp13 | 1530 | 1587 | running |
| chimp13 | 1530 | 1471 | running |
| chimp13 | 1530 | 1471 | running |
| chimp13 | 1530 | 1416 | running |
| chimp13 | 1530 | 1477 | running |
| chimp13 | 1530 | 1416 | running |
| chimp13 | 1530 | 1477 | running |
| chimp13 | 1530 | 1493 | running |
| chimp13 | 1530 | 1520 | running |
| chimp13 | 1530 | 1518 | running |
| chimp13 | 1530 | 1502 | running |
| chimp13 | 1530 | 1598 | running |
+---------+--------+--------+---------+
20 rows in set (0.00 sec)
mysql>
..... the hostId are not the same althought i do a LEFT JOIN on them ...
but sometimes the answer is ok ...
here is my.cnf for a Xeon 2.40Ghz * 2 6 Gig of RAM
# This is for a large system with memory of 1G-2G where the system runs mainly # MySQL.
# The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking skip-external-locking skip-grant-table # added by dlp log-slow-queries log-error
key_buffer = 512M # same that key_buffer_size ? dlp max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 100M # ori=2M read_rnd_buffer_size = 100M # ori= 8M myisam_sort_buffer_size = 64M thread_cache = 1024 # ori =8 query_cache_size = 100M # ori = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4
max_connections = 1000 # dlp key_buffer_size = 512M # dlp
server-id = 1
# Point the following paths to different dedicated disks tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname
innodb_data_home_dir = /var/lib/mysql/innodb/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/innodb/ innodb_log_arch_dir = /var/lib/mysql/innodb/
# You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50
innodb_file_per_table=off innodb_table_locks=off # dlp
[mysqldump] quick max_allowed_packet = 16M
[mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates
[isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 2M write_buffer = 2M
[myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 2M write_buffer = 2M
[mysqlhotcopy] interactive-timeout
--
Dilipan
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
