Index Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Index Question

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-01-08 : 09:02:48

Yes another index question.

-mySQL
-winXP

If I have these tables:


CREATE TABLE tblusers (
	idUser tinyint(3) NOT NULL auto_increment,
	username varchar(10) NOT NULL default '',
	password varchar(10) NOT NULL default '',
	emailadres varchar(30) NOT NULL default '',
		
	PRIMARY KEY  (idUser),
	UNIQUE KEY username (username)
) TYPE=InnoDB COMMENT='Login gegevens';

And


CREATE TABLE tblfoto (
	idFoto tinyint(3) NOT NULL auto_increment,
	idUser tinyint(3) NOT NULL default '',
	fotoNaam varchar(20) NOT NULL default '',
	fotoBeschrijving varchar(50) default NULL,
		
	PRIMARY KEY  (idFoto),
	KEY idUser (idUser),
        INDEX iUser(idUser),
	FOREIGN KEY (idUser) 
		REFERENCES tblusers (idUser)
		ON DELETE CASCADE
) TYPE=InnoDB COMMENT='De foto(''s) van de kandidaten';

And also


CREATE TABLE lnkmuziek (
	lnkIdMuziek tinyint(3) NOT NULL auto_increment,
	idUser tinyint(3) NOT NULL default '',
	idMuziek tinyint(3) NOT NULL default '',
	
	PRIMARY KEY  (lnkIdMuziek),
	KEY idUser (idUser),
	INDEX iUser(idUser),
	FOREIGN KEY (idUser) 
		REFERENCES tblusers (idUser)
		ON DELETE CASCADE
) TYPE=InnoDB COMMENT='Link tabel tussen user en muziek';

Can I just name the INDEX names the same? iUser? Or do I have to use everytime a different index?

And my last question, does anybody know a performance program to test the speed of the mySQL database?

thanxx
Bjorn

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-01-08 : 11:34:24

Sorry, I don't think anyone here can help you on this one. This forum is for Microsoft SQL Server support, not mySQL.

go here instead http://lists.mysql.com/

-ec

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-08 : 12:45:16

or here:

http://www.dbforums.com/f5/

or here:

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=7

btw...the first is a syntax question, which you should easily be able to test...my own opinion (MOO) make them unique...tbnem_ix1

And second...speed in relation to what?

All things are relative, and nothing is compared in a vaccuum...not hardware...volume...ect..

Good Luck...

Brett

8-)

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-01-09 : 03:08:24

thanxx for the answer, and I dont think my Index question I a real mySQL question. I think it belongs to all database types.

And my second question indeed does not belong in this topic. But I was too lazy to make two topics

The speed question is offcours the difference between with or without index's.

Thanxx
Bjorn

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-10 : 15:10:30

quote:
thanxx for the answer, and I dont think my Index question I a real mySQL question. I think it belongs to all database types.

But the answer depends exactly on which database product you use. Some of them may allow it, some of them definitely don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources