Mysql multiple columns index

Question

UPDATE album SET x=1 WHERE store_id=:store_id && type=:type && time<:time

I have a Mysql update query, My question is how can I set up the index for this query

I create index in phpMyadmin, should I select store_id, type, time together and create one index?

Do you know what index is used for at all?

N.B.
– N.B.

2014-02-26 13:58:49 +00:00
Commented Feb 26, 2014 at 13:58 — N.B.
– N.B., Commented Feb 26, 2014 at 13:58
Try not to use && in SQL. The operator is AND.

ypercubeᵀᴹ
– ypercubeᵀᴹ

2014-02-26 14:00:51 +00:00
Commented Feb 26, 2014 at 14:00 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Feb 26, 2014 at 14:00

echo_Me · Accepted Answer · 2014-02-26 14:25:56Z

1

if you are searching by store_id and type and time together then yes you can create INDEX for those three.

BUT,

if sometimes you are searching only by store_id then here you should use index only in store_id

if you search by store_id and type then index will be on those two columns.

so it depeneds what are columns you using to search.

here how to use to create what index you want.

  ALTER TABLE `album` ADD INDEX `myindex` (`store_id`) --for store_id
  ALTER TABLE `album` ADD INDEX `myindex` (`store_id` ,`type`,`time`) --for store_id and type and time
  and so on ....

choose which one you want.

edited Feb 26, 2014 at 14:25

answered Feb 26, 2014 at 13:59

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

6 Comments

Ben Over a year ago

what if i have another query search store_id only, do I need to create 2 index, 1-store_id only 2.three columns together?

echo_Me Over a year ago

only store_id you need , for better performanse use only index on store_id.

echo_Me Over a year ago

you dont have to make index on time.

SebastianH Over a year ago

@BenWong Yes! If sometimes you query for store_id only and sometimes for store_id and other columns then you have to create a separate index for each case. In your case you could also have several separate indices: (store_id), (store_id, type), (store_id, time), (store_id, type, time), ... But adding indices makes insert/update/delete statements much slower, so dont add them if it is not necessary.

Ben Over a year ago

in this case do I need put time into index too? Gordon Linoff's answer put time into it

|

Gordon Linoff · Accepted Answer · 2014-02-26 14:09:27Z

0

When setting up an index, the place to start is the where clause:

WHERE store_id=:store_id && type=:type && time<:time

Start with the equality comparisons. Then you can choose one column for inequality. For this query, the best index would have all three columns:

create index album_storeid_type_time on album(store_id, type, time);

answered Feb 26, 2014 at 14:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Mysql multiple columns index

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related