MySQL performance query. I've got a puzzle which is as follows:
I've got a new table which I'll be loading with approx 1 million rows per day. This is being loaded from a C program which seems to work fine. The table being loaded is as follows: | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------------------+------ ----------+ | startt | datetime | | | 0000-00-00 00:00:00 | | | endt | datetime | | | 0000-00-00 00:00:00 | | | ip | varchar(15) | | MUL | | | | mac_address | varchar(17) | | MUL | | | | openflag | tinyint(3) unsigned | YES | | NULL | | | lastudate | datetime | | | 0000-00-00 00:00:00 | | | rowid | bigint(15) | | PRI | NULL | auto_increment | | updates | int(11) | YES | | 0 | | | lastaction | varchar(8) | | | | | +-------------+---------------------+------+-----+---------------------+---- ----------- There are two indexes : +----------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +----------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+---------+ | tmp_dhcp | 0 | PRIMARY | 1 | rowid | A | 51575 | NULL | NULL | | | tmp_dhcp | 0 | dhcp_ix2 | 1 | mac_address | A | 25787 | NULL | NULL | | | tmp_dhcp | 0 | dhcp_ix2 | 2 | ip | A | 51575 | NULL | NULL | | | tmp_dhcp | 0 | dhcp_ix2 | 3 | openflag | A | 51575 | NULL | NULL | | | tmp_dhcp | 0 | dhcp_ix2 | 4 | startt | A | 51575 | NULL | NULL | | | tmp_dhcp | 0 | dhcp_ix1 | 1 | ip | A | 25787 | NULL | NULL | | | tmp_dhcp | 0 | dhcp_ix1 | 2 | mac_address | A | 51575 | NULL | NULL | | | tmp_dhcp | 0 | dhcp_ix1 | 3 | openflag | A | 51575 | NULL | NULL | | | tmp_dhcp | 0 | dhcp_ix1 | 4 | startt | A | 51575 | NULL | NULL | | +----------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+---------+ I have two updates: Update tmp_dhcp set ....... where ip='X' and mac_address!='Y' and openflag=1 Update tmp_dchp set where mac_address='Y' and ip_address!='X' and openflag=1 The issue is that the first update works at the speed of light - the second update takes approx 2 seconds (when the table has been loaded to 160,000 rows). Using Explain Select on the second update clause gives me a 'const' on index 2 but it's blatantly obvious from the handler_read_rnd_next figures that it's scanning - I had to log slow queries to identify the precise culprit. - but it's consistent. I'm aware that there's been some discussion on index sizes but these two indexes are identical. Any suggestions so that I could at least understand the reason for the table scanning would be greatly appreciated - this has been doing my head in for the last week!. Rgds E-mail: [EMAIL PROTECTED] ---------------------------------------------------------------------------- -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. ============================================================================ == ------------------------------------------------------------------------------ Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. ============================================================================== --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
