MySQL Forums
Forum List  »  MyISAM

Re: Problem with multiple queries using a composite index
Posted by: P. De Maziere
Date: April 04, 2011 03:17AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Problem with multiple queries using a composite index
1642
April 04, 2011 03:17AM


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.