troubleshooting Question

Optimising sql query

Avatar of basil365
basil365Flag for Ireland asked on
DatabasesMySQL ServerSQL
4 Comments1 Solution480 ViewsLast Modified:
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)

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)

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)

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)

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$
);

The database is mysql.

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

thanks
ASKER CERTIFIED SOLUTION
YZlat

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros