mysql - alternative / efficient / optimized query

Question

I want alternative / efficient / optimized query for following query:

Table:

CREATE TABLE `bartco_web_vms_studio`.`table_name` (
      `index` INT( 10 ) NOT NULL AUTO_INCREMENT ,
      `id1` INT( 10 ) NOT NULL DEFAULT '0',
      `id2` VARCHAR( 10 ) NOT NULL,
      `f3` TINYINT( 4 ) NOT NULL DEFAULT '0',
      PRIMARY KEY ( `index` )
) ENGINE = MYISAM ;

Composite index:

CREATE INDEX id1_id2 ON tablename (id1, id2);

Number of rows = 7891

Update query:

UPDATE table_name 
SET f3=1 
WHERE id1=1 AND id2='a' 
   OR id1=2 AND id2='b'
   OR id1=3 AND id2='c'

Basically I have to update a field's value based on 2 fields (id1, id2) values. These 2 fields can be in more than 1 pair.

Output from EXPLAIN SELECT f3 FROM table_name WHERE ...:

id -> 1,
select type -> SIMPLE,
table -> table_name,
type -> range,
possible_keys -> id1_id2,
key -> id1_id2,
key_len -> 261,
ref -> NULL,
rows -> 2,
Extra -> Using where

Thanks a lot for help

Regards

@InSane - good question, +1. Just to expand on this, the ideal index includes both id1 and id2 in the one index. Just wanted to state that as this is a beginner question. — Will A
– Will A, Commented Dec 1, 2010 at 3:43
You're aware that the provided query isn't going to pair the id1 & id2 values as you've declared -- right? You need to use brackets for it to be evaluated properly. — OMG Ponies
– OMG Ponies, Commented Dec 1, 2010 at 3:43
@OMG AND is higher precendence than OR, so I don't think brackets are required. That said, I always feel more comfortable putting them in - and a bracket around the entire set of OR'ed clauses protects against the only-too-easy adding of an additional clause to the end and getting "the wrong behaviour". — Will A
– Will A, Commented Dec 1, 2010 at 3:46
@Will A: mysql optimizer is very fickle about OR. So even on much data index can be ignored. — zerkms
– zerkms, Commented Dec 1, 2010 at 4:13

zerkms · Accepted Answer · 2010-12-01 04:18:18Z

2

Composite index id1 + id2 and query

WHERE (id1, id2) IN ((1, 'a'), (2, 'b'), (3, 'c'))

should help

Also, when you're even testing - add more rows into the table.

Since you're selecting 3 rows of 9 - mysql can decide never to use index at all.

UPD:

if nothing from our answers will help - you can always split your query to 3:

SELECT * FROM table WHERE id1 .. AND id2 ..
UNION
SELECT * FROM table WHERE id1 .. AND id2 ..
...

edited Dec 1, 2010 at 4:18

answered Dec 1, 2010 at 4:06

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

user427969 Over a year ago

Thanks for replying. Is this query optimized? I mean i may have huge data in table so will this work faster or the one that i said ???

zerkms Over a year ago

@user427969: yes, it should be better. Just fill your table with random data and check it.

Will A Over a year ago

This is certainly a better looking rephrasing of the original query - I'd expect the MySQL engine to cope well with either version.

user427969 Over a year ago

I add 2160 rows and tested explain still i get the same ouput

zerkms Over a year ago

@user427969: and what if you left only one pair? Also - update your question with actual table schema (SHOW CREATE TABLE)

|

Collectives™ on Stack Overflow

mysql - alternative / efficient / optimized query

1 Answer 1

18 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Related