[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

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

0
Johnny
Asked:
Johnny
  • 3
  • 2
  • 2
2 Solutions
 
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
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now