mysql select for checking if a entry exist

Question

i had a large databse and had to chec if a email is in a special group I had to tables.

first tabel called user

CREATE TABLE user ( uid int(10) NOT NULL AUTO_INCREMENT, kid int(3) NOT NULL, Email varchar(255) DEFAULT NULL, PRIMARY KEY (uid), KEY kid (kid) ) ENGINE=MyISAM

and a table named group

CREATE TABLE `group` ( `vuid` int(10) NOT NULL AUTO_INCREMENT, `uid` int(3) NOT NULL, `vid` int(3) NOT NULL, PRIMARY KEY (`vuid`) ) ENGINE=MyISAM

and for every insert i had a defined vid and a email Now i had to check, if a user with the uid is in the group vid.

select a.email,b.vuid from user a, group b where a.email=\''.$email.'\' and a.kid=' . $kid.' and b.vid=' . $vid . ' and a.uid = b.uid limit 1')

and check if the mysql_fetch_assoc is true or false.

BUT this is verry verry slow. is there a simple way to speed up ?

Why are you checking the email as well?

Michael Mior
– Michael Mior

2011-06-10 16:10:12 +00:00
Commented Jun 10, 2011 at 16:10 — Michael Mior
– Michael Mior, Commented Jun 10, 2011 at 16:10

Dereleased · Accepted Answer · 2011-06-10 16:10:43Z

Rather than using a comma separated list of tables, try using a JOIN (in this case, INNER JOIN is your best bet since you want to check if records exist in both tables) with an ON clause. I have formatted your query and my changes are in capitals to make them stand out.

select a.email, b.vuid from user a INNER JOIN group b ON a.uid = b.uid where a.email=\''.$email.'\' and a.kid=' . $kid.' and b.vid=' . $vid . ' limit 1

Next, check your indexes - make sure you have indexes on b.uid and a.kid. As a general rule, check your where clauses for values you can index; anything with unique values is a candidate.

datasage · Accepted Answer · 2011-06-10 16:06:25Z

1

If its slow, you might be missing indexes on either the lookup column or on the foreign key column.

answered Jun 10, 2011 at 16:06

datasage

19.7k2 gold badges51 silver badges55 bronze badges

4 Comments

datasage Over a year ago

Any column that you are using as a foreign key should be defined the same as the key in the foreign table and have an index. This way Mysql can handle the join quickly. If you don't do that, it will have to use slower table scan methods to join the tables.

Roby Over a year ago

:-/ could you please help me ? like ALTER TABLE group ADD FOREIGN KEY (uid) REFERENCES user(uid) ??

datasage Over a year ago

You dont need to add foreign key constraints, you just need to add indexes.

Roby Over a year ago

does i have to restartv the db ? because it istn faster... think slower

Johan · Accepted Answer · 2011-06-10 16:11:15Z

Do:

ALTER TABLE `group` ADD INDEX `uid`(uid); ALTER TABLE `user` ADD INDEX `email`(`Email`);

This should fix your slowness.

Collectives™ on Stack Overflow

mysql select for checking if a entry exist

3 Answers 3

Comments

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Related