Re: Problem with multiple queries using a composite index
Hello Thank you for the swift answer.
I did some further research as well, and I'm sorry but I have to correct my question slightly. But before that I will respond to some of your questions:
It's a heavy workstation running Fedora Core 11: a recent Xenon quad core (harpertown) with 20GB RAM and fast hard disks with lots of cache as well. Each InnoDB table has its own ibd file (instructed to do so by my.cnf). Further things to know from that my.cnf:
innodb_buffer_pool_size = 5G
innodb_data_home_dir = /data2_siren/mysql/ibdata
innodb_data_file_path = ibdata1:10M:autoextend:max:2G
innodb_file_per_table = 1
innodb_log_buffer_size = 8M
tmp_table_size = 2G
tmpdir = /dev/shm
query_cache_type = 1
query_cache_size = 2G
query_cache_limit = 3G
myisam-recover=backup,force
And yes, I'm aware of the cache. So I did not run exactly the same query, but changed a value when trying the other query. However, I did it that way that both different values return the same amount of rows with rather similar content, just to avoid cache speed-ups and to avoid different timings due to very different result sets.
But, to come back to my first remark. I also made mysql to log long queries, because I came a bit suspicious, and they showed me that it was not the select query that was eating up time, but an update query coming after that one and which was missed by my output generator (he only tracked the selects since I suspected them...).
The update query had this, similar, form:
UPDATE MyTable SET Field7=a WHERE (Field1, Field2, Field3, Field4, Field5) = (1, 1, 2, 3, 5) AND Field6 = 6;
which did take tremendously more time (about 1 minute to 1 minute and a half) compared to the query
UPDATE MyTable SET Field7=a WHERE Field1=1 AND Field2=1 AND Field3=2 AND Field4=3 AND Field5=5 AND Field6 = 6;
Which did the job within milliseconds. (Fields 1 up to 5 are part of composite key, field 6 is not, hence the maybe strange way of writing down the WHERE clause for the first kind of query).
Meanwhile, I also added just a single identifier to the table that I use as primary key (I read somewhere, that with InnoDB tables, the primary key is added to every other key. So in order to reduce the needed space, I inserted that primary key.)
I have to say that InnoDB performs the first update faster than the identical MyISAM table but still much slower than the second kind of query. So, I wonder what the explanation can be. Are some internal MySQL routines/structures involved that work completely different or?
Many thanks in advance,
Patrick