[SOLVED] Trying to do it all in one query = death / Your solution?

[exitof99]

Okay, I've developed a site that uses modules which each have their own table in MySQL. These modules each can store different data sets and are linked together by a another table which contains general information.

 

Here is a simplified example, hope you can follow:

 

1. User Table

   a. user_id

   b. user_ip

 

2. Module 1 - Phone Table

  0. mod1_id

  a. mod1_user_id

  b. mod1_phone

 

3. Module 2 - Address Table

  0. mod2_id

  a. mod2_user_id

  b. mod2_address

  c. mod2_city

  d. mod2_state

  e. mod2_zip

 

4. Module 3 - Favorite Foods Table

  0. mod3_id

  a. mod3_user_id

  b. mod3_food1

  c. mod3_food2

 

The best time results I get by unrolling the query in to several queries:

 

$ids = mysql_query("SELECT user_id,user_ip FROM usertable WHERE 1");
while ($id = mysql_fetch_assoc($ids){
$mod1 = mysql_query("SELECT mod1_phone FROM mod1 
WHERE mod1_user_id='$id' LIMIT 1;");
$mod2 = mysql_query("SELECT mod2_address,mod2_city,mod2_state,mod2_zip FROM mod2 
WHERE mod2_user_id='$id' LIMIT 1;");
$mod3 = mysql_query("SELECT mod3_food1,mod3_food2 FROM mod3 
WHERE mod3_user_id='$id' LIMIT 1;");

... process ...

}

 

This beats using LEFT JOIN conditions:

SELECT user_id,user_ip,mod1_phone,mod2_address,
mod2_city,mod2_state,mod2_zip,mod3_food1,mod3_food2 
FROM usertable LEFT JOIN mod1 ON (user_id=mod1_user_id) 
LEFT JOIN mod2 ON (user_id=mod2_user_id) 
LEFT JOIN mod3 ON (user_id=mod3_user_id) WHERE 1;

 

The real site has a lot of data, so LEFT JOIN statements are being costly. I tried swapping out the LEFT JOINS with INNER JOINS and the speed went from 10 seconds to under 1 second, but the rows were not returned if data was missing from one of the mods, so this is not a solution.

 

My question is to the public is what method is best used to grab all the data in one query? Keep in mind that each module always has an id value referring back to the user table, so the user table is the master table and the others are children.

 

I've tried UNION and SELECT statements with no success, not really familiar with UNION though. I did do inline SELECT statements in the field list which works quickly, but is bulky and not so pretty:

 

SELECT user_id,user_ip,
(SELECT mod1_phone FROM mod1 WHERE mod1_user_id = user_id), 
(SELECT mod2_address FROM ... ), 
(SELECT mod3_food2 FROM mod3 WHERE mod3_user_id = user_id) 
FROM usertable WHERE 1;

 

I wondered about using a unified key ID name, not sure if that would help MySQL (all mods using user_id specifically instead of having the prefix attached like mod1_user_id).

 

Thanks in advance if you read through this and give it some thought.

[fenway]

union would only be a good fit if you had the same number of columns

[exitof99]

Okay, no one responded to this with any solutions, but I have since figured out what was happening. I found a page taking about indexing for joins on the interwebs that explained in-depth what was happening in the join process, how to read EXPLAIN output, and what needs to be done.

 

In the end, the query I had been using was perfect. It was the table structure that was at fault. For all modules, the user_id column needed to be set to 'Unique' or be the primary key in order for the LEFT JOIN statements to know what is what. Upon doing so, the query went from trying to kill the server with an hour long process to a fraction of a second.

 

The answer:

Be sure index for joins properly! Common linking columns need to be set to primary or unique.

[fenway]

If you actually read the posting guidelines for this forum, you'd see that we request your table structure AND the EXPLAIN output, which would have told us right away what the problem was.