how to use mysql index,primary correctly in my sql database

Johnny
Johnny used Ask the Experts™
on
i was wondering how to use index,primary and those selections in myphpadmin(or any mysql database)
would it be better if i used index on the tag_name field in this case so its searches easier as that be the primary thing id be looking at, i always put it as  a default of id as i never understood how to use it

can someone please enlighten me how i may best use it on my table thanks
thanks in advance for any help you may provide
Johnny

my sql
CREATE TABLE IF NOT EXISTS `auto_tags` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(50) NOT NULL,
  `tag_words` text NOT NULL,
  `tag_color` varchar(7) NOT NULL COMMENT '#000000 format',
  `notes` text NOT NULL,
  `admin_notes` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `auto_tags`
--

INSERT INTO `auto_tags` (`id`, `tag_name`, `tag_words`, `tag_color`, `notes`, `admin_notes`) VALUES
(1, 'Political', '''bipartisan'', ''government'', ''politics'', ''republican'',''democratic'',''president''', '#BADA55', '', ''),
(2, 'Religious or Spiritual', '''God'', ''Church'', ''Clergy'', ''Pastor'', ''Priest'', ''Christian'', ''Catholic'', ''baptize'', ''baptism'', ''Baptists'', ''Bar Mitzvah'', ''bible'', ''Biblical'', ''Bishop'', ''Lutheran'', ''Lutheranism'','' Liturgy'', ''Lord'', ''Lucifer'', ''Satan'', ''Passover'' ', '#BADA55', '', '');

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016
Commented:
In MySQL the index serves as a way of getting "in-memory" performance from a SELECT query instead of "on the disk" access times, which are generally orders of magnitude slower.  As a general rule, you want to have an index on any column that is used in WHERE, JOIN, HAVING, ORDER, BETWEEN, etc.
Commented:
OK, let's see if I can make it simple.

You almost always have an id field that is auto incremented. This ensures every record in your table has an unique field that can be used in joins.
 
Why do you need joins? To get your data, of course. When designing the structure of your database you won't usually use a single table, you'll normalize your data and end up with multiple tables that have a common field.

Indexes are like a directory or an address book. MySQL uses these to quickly find your data. So your queries will work even without indexes, but usually will be hundreds of times slower.

HTH,
Dan

Author

Commented:
@ Ray_Paseur
always a pleasure to see you field a post Ray.

so in this case then if i do "SELECT `tag_words` WHERE `tag_name`="Political" " my index should be on field name `tag_name` is that correct if I understand this correctly

how would i set the primary and key or can i only set index or something else...???

thanks for the help as always.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
@ Dan Craciun
thx for the reply.

So if i use multible tables with id as the common lookup ie user info and user activity tables
user_info having id,name,address,email etc
user_activity having id,user_id,blah blah,blah blah fields
then id have id as index on user_info table
and user activity would be anything as a fieldname where something = fieldname, one that i use the most as an index, as then it looks it up faster???

did i get that right?

Commented:
about right.

SELECT whatever FROM user_info, user_activity WHERE user_info.id = user_activity.user_id
or
SELECT whatever FROM user_info JOIN user_activity ON user_info.id = user_activity.user_id

will be way faster if you have indexes on id and user_id

Author

Commented:
thank you both
Most Valuable Expert 2011
Top Expert 2016

Commented:
Thanks for the points.  You can have any number of indexes, covering individual columns and combinations of columns.  About the only time an index is a negative is when you're doing a lot of INSERT or UPDATE with queries that change the indexed columns.  This is because the entire index structure needs to be corrected when there are new values in the indexed columns.  Usually (almost always) the benefits of the index outweigh the cost.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial