MySQL Pros: How would you build this complex query?
 

Hi,

I am trying to come up with the most optimal way to write a query to select out the following fields:

test_username.username_name
test_subprofile_details.subprofile_details_headlin e
test_profile.profile_birthday
test_profile_gender.profile_gender_name
test_subprofile.subprofile_picture
test_country_region.country_region_name
test_country.country_name
test_profile_orientation.profile_orientation_name (there could be multiple rows)

With the following conditions:

profile_orientation_id IN (1,2)
profile_gender_id = 1
country_id = 1
site_id = 3

ORDER BY subprofile_last_update DESC
LIMIT 30


I am using MySQL 4.1 so subqueries are a valid option. I know I could do many queries to get my result, but I'm trying to see how I could do this in 1 query if possible, or the least amount of queries possible otherwise. I assume complex joins and/or subqueries need to be used, but I'm not very experienced with them.

Any help pointing me in the right direction, or giving me a solution (so then I could see how it was done for use in my future queries) would be greatly appreciated. Thanks in advance!

Tables Involved:

mysql> desc test_username;
+---------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | | PRI | 0 | |
| username_name | varchar(20) | | UNI | | |
+---------------+-----------------------+------+-----+---------+-------+

mysql> desc test_subprofile_details;
+----------------------------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | | MUL | 0 | |
| site_id | tinyint(1) | | MUL | 0 | |
| subprofile_details_headline | varchar(255) | | | | |
| subprofile_details_description | text | | | | |
| subprofile_details_description_seeking | text | | | | |
+----------------------------------------+-----------------------+------+-----+---------+-------+

mysql> desc test_subprofile;
+----------------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+-----------------------+------+-----+---------+----------------+
| subprofile_id | int(10) unsigned | | PRI | NULL | auto_increment |
| uid | mediumint(8) unsigned | | MUL | 0 | |
| site_id | tinyint(1) | | MUL | 0 | |
| subprofile_status_id | tinyint(2) | | MUL | 0 | |
| subprofile_last_update | int(10) unsigned | | MUL | 0 | |
| subprofile_picture | tinyint(1) | | MUL | 0 | |
| subprofile_voice | tinyint(1) | | MUL | 0 | |
| subprofile_video | tinyint(1) | | MUL | 0 | |
| subprofile_picture_gallery | tinyint(1) | | MUL | 0 | |
| subprofile_visits | int(10) unsigned | | | 0 | |
| subprofile_votes | mediumint(8) unsigned | | | 0 | |
| subprofile_rating | tinyint(2) | | | 0 | |
+----------------------------+-----------------------+------+-----+---------+----------------+

mysql> desc test_profile;
+---------------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | | PRI | 0 | |
| profile_birthday | int(10) unsigned | | MUL | 0 | |
| profile_gender_id | tinyint(1) | | MUL | 0 | |
| profile_marital_status_id | tinyint(1) | | MUL | 0 | |
| profile_body_type_id | tinyint(1) | | MUL | 0 | |
| profile_height | tinyint(3) | | MUL | 0 | |
| profile_zip | varchar(10) | | MUL | | |
| country_id | tinyint(3) unsigned | | MUL | 0 | |
| country_region_id | mediumint(8) unsigned | | MUL | 0 | |
| profile_city | varchar(80) | | | 0 | |
| profile_heritage_id | tinyint(1) | | MUL | 0 | |
| profile_faith_id | tinyint(1) | | MUL | 0 | |
| profile_smoke_id | tinyint(1) | | MUL | 0 | |
| profile_drink_id | tinyint(1) | | MUL | 0 | |
| profile_pet_id | tinyint(1) | | MUL | 0 | |
| profile_children_id | tinyint(1) | | MUL | 0 | |
| profile_allow_rating | tinyint(1) | | | 0 | |
+---------------------------+-----------------------+------+-----+---------+-------+

mysql> desc test_country;
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| country_id | tinyint(3) unsigned | | PRI | NULL | auto_increment |
| country_code | char(2) | | UNI | | |
| country_name | varchar(50) | | UNI | | |
+--------------+---------------------+------+-----+---------+----------------+

mysql> desc test_country_region;
+---------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-----------------------+------+-----+---------+----------------+
| country_region_id | mediumint(8) unsigned | | PRI | NULL | auto_increment |
| country_region_code | char(2) | | MUL | | |
| country_region_name | varchar(100) | | | | |
+---------------------+-----------------------+------+-----+---------+----------------+

mysql> desc test_country_link;
+-------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+-------+
| country_id | tinyint(3) unsigned | | MUL | 0 | |
| country_region_id | mediumint(8) unsigned | | MUL | 0 | |
+-------------------+-----------------------+------+-----+---------+-------+

mysql> desc test_profile_orientation;
+--------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-------------+------+-----+---------+----------------+
| profile_orientation_id | tinyint(2) | | PRI | NULL | auto_increment |
| profile_orientation_name | varchar(15) | | | | |
+--------------------------+-------------+------+-----+---------+----------------+

mysql> desc test_profile_orientation_link;
+------------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | | MUL | 0 | |
| profile_orientation_id | tinyint(2) | | MUL | 0 | |
+------------------------+-----------------------+------+-----+---------+-------+

mysql> desc test_profile_gender;
+---------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+----------------+
| profile_gender_id | tinyint(1) | | PRI | NULL | auto_increment |
| profile_gender_name | varchar(15) | | | | |
+---------------------+-------------+------+-----+---------+----------------+

$time X hours = $$$
hire someone, do something for the economy?!

I am not going to do this for you for free but I can tell you this much. The performance will depend on your indexing moreso than the query itself although both are important.

The same way you wrote it out in laymans...

I have no problem hiring someone to do it if it was something important....

However since I'm asking because I'd like to learn it myself, I'd rather see the solution, that way I can see what was done exactly.

I'm not having any performance issues or problems at the moment, I'd just like to know how best this can be done.

Those who can give me a professional way to write the query using joins or subselects, send me your price anyways though [email protected] or ICQ 38373031

Keith,

I copied and sent to one of my coders who has a buddy who might be able to figure this for you.

I'll let you know asap.

K

Cool....I have it done one way which works fine, but for my own curiosity I'd like to know the "real" way to do it (ie. the way pros would do it)

thx