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

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

JohnnyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dan CraciunIT ConsultantCommented:
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
0
JohnnyAuthor 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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

JohnnyAuthor 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?
0
Dan CraciunIT ConsultantCommented:
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
0
JohnnyAuthor Commented:
thank you both
0
Ray PaseurCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.