MySQL Pros: How would you build this complex query?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Varius
    Confirmed User
    • Jun 2004
    • 6890
    #1

    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) | | | | |
    +---------------------+-------------+------+-----+---------+----------------+
    Skype variuscr - Email varius AT gmail
    Tags: None
    • xenophobic
      Confirmed User
      • Mar 2004
      • 874
      #2
      $time X hours = $$$
      hire someone, do something for the economy?!

        Comment

        • mikesouth
          Confirmed User
          • Jun 2003
          • 6340
          #3
          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.
          Mike South

          It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

            Comment

            • V_RocKs
              Damn Right I Kiss Ass!
              • Nov 2003
              • 32467
              #4
              The same way you wrote it out in laymans...

                Comment

                • Varius
                  Confirmed User
                  • Jun 2004
                  • 6890
                  #5
                  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
                  Skype variuscr - Email varius AT gmail

                    Comment

                    • KRL
                      Entrepreneur
                      • Oct 2002
                      • 31429
                      #6
                      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
                      If you would like to develop your domains, you can lease inexpensive foreign labor
                      from the leaders in the field at iWebmasters.com TO LOWER YOUR COSTS AND INCREASE YOUR PRODUCTION!
                      *** *** *** *** *** *** *** *** *** *** *** ***
                      Domains Adult News KRL's Newsletter Biz Tips Just Listed Domains

                        Comment

                        • Varius
                          Confirmed User
                          • Jun 2004
                          • 6890
                          #7
                          Originally posted by KRL
                          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
                          Skype variuscr - Email varius AT gmail

                            Comment

                            Previous template Next
                            Working...