Optmized query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Optmized query?

Post by alex.barylski »

[sql]SELECT DISTINCT    persons.emailFROM    persons, groupsWHERE (        groups.fkid $IN        AND        groups.personid = persons.pkid        AND        persons.STATUS = 1    )[/sql]

I changed the tables names here so pardon any invalidities with mis-matching tables, fields, etc...

Basically, the gist of it is...I have a table which a list of people. And another table which holds a list of groups those people belong too - only selecting people who are active members (status = 1).

There are a huge potential for duplicates of emails because one person can belong to N number of groups thus the DISTINCT.

Is this query as optimized as it could be, are there any MySQL specific tricks? Each table has a PKID which is a primary key - auto-incrementing...

Thre The $IN variable is a IN clause constructed in PHP and secure. I am not worried of IN growing to massive array of ID's maybe a dozen at most...

Other that that...is it opimized? Could be further refined? The tables have been normalized and persons table is quit minimal (5 field - none of which are variable).

Cheers :)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Optmized query?

Post by Weirdan »

And what is output of EXPLAIN for your query?
Post Reply