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

Posted on 2014-08-03
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

my sql
  `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`)

-- 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

Question by:Johnny
    LVL 107

    Accepted Solution

    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.
    LVL 34

    Assisted Solution

    by:Dan Craciun
    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.


    Author Comment

    @ 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 Comment

    @ 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?
    LVL 34

    Expert Comment

    by:Dan Craciun
    about right.

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

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

    Author Closing Comment

    thank you both
    LVL 107

    Expert Comment

    by:Ray Paseur
    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now