1

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!!

Samuel Lui
  • 159
  • 1
  • 1
  • 11
  • 1
    2nd one, and if you ever see someone using design shown in your first example - don't talk to that person ever again. On the other hand, don't do it yourself also, it's terrible on multiple levels (it's not even logical to do it, even from non-programmer point of view). – N.B. Sep 26 '12 at 10:03
  • haha, i just curious case A also work but i dont see anyone do like this, but i need to know the reason?! – Samuel Lui Sep 26 '12 at 10:12
  • i find something interesting [stackoverflow|mysql-like-performance-boost](http://stackoverflow.com/questions/2481528/mysql-like-performance-boost) – Samuel Lui Sep 26 '12 at 10:15
  • Erm, because it completely defeats the purpose of using the database. You have to scan every record to find something beginning with "1_". It's not clear what every thing after each underscore is, and in the end - is it easier to scan ALL the records against scanning the records only belonging to user with ID = 1, from computation point of view? Basically - nothing in the first approach is good. – N.B. Sep 26 '12 at 10:15
  • Also, for your last comment - indexes aren't a magic stick that make everything work out of thin air. People often misuse them, thinking everything will just go supersonic in a second. I don't know what your knowledge level is, but the answer you linked and your questions don't have anything in common. – N.B. Sep 26 '12 at 10:16
  • but my search is LIKE '1_%' the wildcard is place at the end. So why u say i need to search all record to know the answer??? Thanks!! – Samuel Lui Sep 26 '12 at 10:34
  • You need to read about indexes and how they work internally or ask another question where people will explain why your initial case is terrible in every possible case. As I said before, indexes aren't magic, and you need to know how they work so you can understand the difference between case A and B. – N.B. Sep 26 '12 at 10:48
  • THANK YOU true-heart to you!! N.B. – Samuel Lui Sep 26 '12 at 12:46

1 Answers1

1

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.

AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
  • First one also uses Index because of position of the percent sign. But you are right second one is faster than first one. – PepeDeLew Sep 26 '12 at 10:09
  • Case A's index is much larger than Case B's index. Case A's index will be completely traversed, Case B's index will take 1 lookup. Indexing in Case A is completely pointless. – N.B. Sep 26 '12 at 10:17
  • It's not "completely pointless". The index is be a B-tree, so it should just find the node for the prefix "1_" and return everything in that subtree. B-trees are very efficient when you're looking for rows with a common prefix. – Barmar Sep 26 '12 at 11:00