CODEWITHSUNDEEP
mysqlindexingmultiple-columns
Samuel Lui
Samuel Lui

Reputation: 169

mysql index VS column

I would like to ask which way to design the table is faster for query?

Case A

image table

id

1_20120930_aaaaa

9_20120930_ccccc

2_20120930_aaaaa

5_20120930_ddddd

3_20120930_vvvvv

1_20120930_bbbbb

SELECT * FORM image WHERE id LIKE '1_%';

Case B

image table

id | date | user_id

aaaa|20120930|1

cccc|20120930|9

aaaa|20120930|2

dddd|20120930|5

vvvv|20120930|3

bbbb|20120930|1

SELECT * FROM image WHERE user_id = '1';

Thank you!!

Upvotes: 1

Views: 56

Answers (1)

AnandPhadke
AnandPhadke

Reputation: 13486

Its definitely the CASE B.Because when you use like operator it will not use the index even if you define index on the id column.So in case B,you can create an index in id and use it in where clause for faster retrieval of data from a table.

Upvotes: 1

Related Questions