CODEWITHSUNDEEP
phpmysqlinner-joinquery-optimization
Richard Jacobs
Richard Jacobs

Reputation: 101

How to speed up INNER JOIN MySQL Query

I'm having performance issues pulling data from 2 sql tables. It is very very slow. When I remove the INNER JOIN the page loads instantly.

$query = "SELECT sc.*, dr.fullName, dr.email, dr.mobile, dr.address1, dr.town, dr.postCode, dr.utr, dr.vatNo, dr.driver_number, dr.pt, dr.siteLoc, dr.vatCharge, dr.vraRate, dr.third_party_provider
FROM scheduling sc
INNER JOIN driverReg dr ON dr.ni = sc.ni
WHERE sc.ref = '".$ref."' AND sc.excluded = 'No'";

Does anyone have a better method of pulling this data faster?

Upvotes: 0

Views: 240

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

be sure you have a composite index on table scheduling column (ref, excluded,ni )

and an index on table driverReg column ni

 create index  myidx1 on scheduling (ref,  excluded,ni )
 create index  myidx2 on driverReg (ni )

anyway not related to performance but for security .. you should avoid the use of php in sql code because you are at risk for sqlijection .. for avoid this you shuold take a look at your mysql driver for prepared statement and binding param

Upvotes: 1

Related Questions