Confusion in mysql query

Question

createTable('messages',
     'id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      auth VARCHAR(16), 
      recip VARCHAR(16),
      pm CHAR(1),
      time INT UNSIGNED, 
      message VARCHAR(4096), 
      INDEX(auth(6)), 
      INDEX(recip(6))');

This is a mysql command for creating a table. I was just wondering, what are the last 2 lines of the command, "INDEX(auth(6))" and "INDEX(recip(6))"? I am used to using phpMyAdmin and used to more of the graphical interface. How can you relate this command to the graphical interface process?

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

In mysql terminal or PHP My admin this is how you can create the table

create table messages(
     id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      auth VARCHAR(16), 
      recip VARCHAR(16),
      pm CHAR(1),
      time INT UNSIGNED, 
      message VARCHAR(250), 
      INDEX (auth), 
      INDEX (recip)
);

Also note that message VARCHAR(4096), does not make sense since it does not serve any purpose.

What is the MySQL VARCHAR max size?

The last 2 commands is to create the index on the columns auth and recip

Here is is what index for http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

edited May 23 '17 at 12:27

Community

1
1

answered May 23 '14 at 20:55

Abhik Chakraborty

44,654
6
52
63

Why does `VARCHAR(4096)` not make sense but `VARCHAR(250)` does? That effectively limits the characters that column will hold to ***4096*** does it not? – War10ck May 23 '14 at 21:00
@War10ck as per doc Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. However for big inputs, its a better idea to use TEXT, MEDIUMTEXTor LONGTEXT instead, and thats my point. – Abhik Chakraborty May 23 '14 at 21:04

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

INDEX(auth(6)) and INDEX(recip(6)) are included to create indexes on the table. Indexes are created, so that queries (like SELECT * FROM messages WHERE auth=<xyz>) would run faster.

The MySQL documentation contains information on creating indexes as well as how they are used by MySQL.

There is a very detailed explanation of how indexing works in this question on SO.

edited May 23 '17 at 10:26

Community

1
1

answered May 23 '14 at 21:00

Joseph B

5,519
1
15
19

Confusion in mysql query

2 Answers2