We help IT Professionals succeed at work.

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

Johnny
Johnny asked
on
365 Views
Last Modified: 2014-08-03
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

Most Valuable Expert 2011
Author of the Year 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dan CraciunIT Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

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.

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?
Dan CraciunIT Consultant
CERTIFIED EXPERT

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
Author of the Year 2014

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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.