0

I have a large table and I have to sort the records using tag_id currently this column is varchar but all the values are numeric in this.

Will there be any performance improvement if I convert column datatype from varchar to integer??

Below is the quesry which I have to write:

SELECT * FROM xxx table ORDER BY tag_id;

Rakesh K
  • 692
  • 4
  • 13
  • 26
  • What type of performance you want ??? – Wasiq Muhammad Sep 22 '16 at 12:49
  • Do you mean convert the column type once, for all rows, as a standard, or you mean re-casting the column type in the SQL query? I would expect the first would be faster than the second. – Martin Sep 22 '16 at 12:51
  • 1
    Define 'large' - i.e. approx how many rows? e.g 100, 1 thousand, 10 thousand, etc. – Ryan Vincent Sep 22 '16 at 12:51
  • 2
    So they are all numbers and the fact you ask this question means you *can* convert to int so you *should* convert on the grounds that you should always use the correct datatype for the data itself. – Alex K. Sep 22 '16 at 12:52
  • @RyanVincent Large means - having 1-2 million records and the tag_id will be 64bit integer only. will it improve the performance if i change varchar to int. – Rakesh K Sep 22 '16 at 13:19
  • As already mention by @AlexK, it will be worth converting. Remember that any table referencing that tag_id column will need to be changed as well. It will be quicker for index access as it will be smaller for the same number of rows. Compares will be faster as well. – Ryan Vincent Sep 22 '16 at 13:21
  • @RyanVincent Converting reference table is not an issue I can do that. Which query will be fast ORDER BY tag_id (where tag_id is varchar) or ORDER BY tag_id (where tah_id is integer)?? – Rakesh K Sep 22 '16 at 13:35
  • 1
    You would need to benchmark the query. Maybe interesting? [Is there a REAL performance difference between INT and VARCHAR primary keys](http://stackoverflow.com/questions/332300/is-there-a-real-performance-difference-between-int-and-varchar-primary-keys). Also: [SQL SELECT speed int vs varchar](http://stackoverflow.com/questions/2346920/sql-select-speed-int-vs-varchar). Seriously, it so depends on the resources the database has access to. i.e. lots of memory, fast disks, appropriate indexes etc. I would convert to int as it feels wrong to use strings when an int is more appropriate. ;-/ – Ryan Vincent Sep 22 '16 at 14:10

0 Answers0