Avatar of basil365
Flag for Ireland

asked on 

Optimising sql query


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:


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)


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) :


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)


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.

a.*,b.*,c.*,d.* from 
tcache_b_active a, tcache_customer b, tcache_b_leg_active c, tcache_b_details_active d
a.custId = b.customerId
a.bsourceId = b.bsourceId
a.bsourceId = c.bsourceId
c.globalId = d.globalId
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?

DatabasesMySQL ServerSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon