Databases
StackOverflow2013
Postsdbo
POTrouble with joining with select or joining with temporary tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble with joining with select or joining with temporary tables
    primarykey
    data
    text
    <p>I have five tables </p> <pre><code>orders(order_id,user_id,) order_info(id,price,tax,discount) users(user_id,uadd_id) user_address(uadd_id,type,city_id) city_info(city_id,city_name,city_code) </code></pre> <p>I need to obtain a report of orders which include order information from <code>order_info</code> table, user_information that usually comes form user table and users shipping and billing address both comes from <code>user_address</code> table. The <code>users.uadd_id</code> maps with <code>user_address.id</code> and the <code>user_address.type</code> stores if it is a shipping or billing address. So for the same order I need two rows of the user_address one for shipping and the other for billing. The <code>city_info</code> table contains the details of city and should be joined with the user address table to get the complete information of the user address.</p> <p>I tried this query </p> <pre><code>select o.*,oi.*,billing_info.name as binfo_name,shipping_info.name_name from order o left join order_info oi on o.order_id = oi.id left join ( select * from user u1 left join user_address ua1 on ua1.uadd_id = u1.uadd_id left join city_info ci1 on ua1.city_id = ci1.city_id where ua1.type = 'billing') as billing_info on billing_info.user_id = o.user_id left join (select * from user u2 left join user_address ua2 on ua2.uadd_id = u2.uadd_id left join city_info ci2 on ua2.city_id = ci2.city_id where ua1.type = 'shipping' ) as shipping_info on shipping_info.user_id = o.user_id where 1; </code></pre> <p>(I have selected other columns for both billing_info and shipping_info) This works and i can get both billing and shipping info but the problem is the query takes a lot of time ( almost 2 seconds) to execute. I have a large set of data in all the tables. so i needed to do something else.</p> <p>I tried temporary table to store all required shipping and billing info and join the order table with these two tables but its still taking a lot of time ( again 2 seconds) here is how I tried to use the temporary table:</p> <pre><code>create temporary table shipping_info select * from user u1 left join user_address ua1 on ua1.uadd_id = u1.uadd_id left join city_info ci1 on ua1.city_id = ci1.city_id where ua1.type = 'billing'; create temporary table billing_info select * from user u1 left join user_address ua1 on ua1.uadd_id = u1.uadd_id left join city_info ci1 on ua1.city_id = ci1.city_id where ua1.type = 'shipping'; select o.*,oi.*,billing_info.name as binfo_name,shipping_info.name_name from order o left join order_info oi on o.order_id = oi.id left join shipping_info on shipping_info.user_id = o.user_id left join billing_info on billing_info.user_id = o.user_id where 1; </code></pre> <p>Can anyone help me on how i can do this efficiently using any of the above ways or some other way?</p> <p>If any errors in query due to syntax, please neglect that for now since i had to change the actual query to shorten wherever possible red data and use this temporary table to join with other table to select the required data. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRyan
    1. USbhab
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POTrouble with joining with select or joining with temporary tables
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTrouble with joining with select or joining with temporary tables
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COIt would be nice if you added the definitions of your tables (including the indexes).
      singulars
      1. POTrouble with joining with select or joining with temporary tables
      1. USypercubeᵀᴹ
    2. COand when you say *"large tables"*, how large do you mean?
      singulars
      1. POTrouble with joining with select or joining with temporary tables
      1. USypercubeᵀᴹ
    3. COEach table has minimum of 20000 rows , and actully i am not selecting all data from all tables its filtered in the where condition so the time taken that i mentioned is for fetching 5000 rows of data which i guess shouldn't take so much time.
      singulars
      1. POTrouble with joining with select or joining with temporary tables
      1. USbhab
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload