We help IT Professionals succeed at work.
Get Started

Optimising sql query

basil365
basil365 asked
on
479 Views
Last Modified: 2021-04-21
Hi,

I have a db with 2 main tables that represent data that we receive in a feed. In each of these 2 tables there is data that is duplicated so i normalised them into 4 separate tables. From a db storage point of view this works very well and saves me loads of space, but i'm having problems creating the query to load this data back into memory when required.

These are my tables:

tcache_b_active

bsourceId      int(11) PK
bId            int(11) PK
ipAddress        varchar(45)
custId           int(11)
channel          varchar(10)
bType          varchar(5)
bPlacedTime    datetime
bReceivedTime  datetime
amount        double
numLegs    int(11)

Open in new window


There is a one to one/many relationship with the following db (joined on bsourceId and bId)

tcache_b_leg_active

bsourceId      int(11) PK
bId            int(11) PK
globalId int(11) PK
tag              varchar(45)

Open in new window


There is also a one to one relationship with a customer table (bsourceId and customerId) :

tcache_customer

bsourceId      int(11) PK
customerId       int(11) PK
userName         varchar(100)
firstName        varchar(45)
lastName         varchar(45)
custRegCode      varchar(45)
custIsElite      tinyint(1)
custRegPostCode  varchar(45)
custRegEmail     varchar(150)
countryCode      varchar(10)

Open in new window


finally the tcache_b_leg table is joined to details table (on globalId)

tcache_b_details_active

globalId int(11) PK
Name       varchar(120)
description varchar(120)
startTime   datetime
result           varchar(1)

Open in new window


my query needs to get all the information from each of the tables when given a globalId. The problem arises because there can be multiple legs per main entry so i need an inner select. This is the query i have tried and it takes far too long.

select 
a.*,b.*,c.*,d.* from 
tcache_b_active a, tcache_customer b, tcache_b_leg_active c, tcache_b_details_active d
where
a.custId = b.customerId
and
a.bsourceId = b.bsourceId
and
a.bsourceId = c.bsourceId
and
c.globalId = d.globalId
and
a.bId in 
(
select bId from tcache_b_leg_active where globalId = $INPUT$
);

Open in new window


The database is mysql.

can you help me optimise this as much as possible or suggest another approach?

thanks
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE