Page 1 of 1

Optmized query?

Posted: Tue Jan 29, 2008 1:53 pm
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 :)

Re: Optmized query?

Posted: Tue Jan 29, 2008 3:11 pm
by Weirdan
And what is output of EXPLAIN for your query?