CODEWITHSUNDEEP
mysqlperformance
Jeremy
Jeremy

Reputation: 973

How can I optimize this mysql query please?

Can anyone help me to optimize this query ? I can probably add some indexes and get only date from the modified (CURRENT_TIMESTAMP) field ?

Thanks Jeremy

SELECT a.providerName AS Assureur, b.insuranceType AS Produit, c.votedFor, c.votedFor2, c.email, c.comment, c.modified
FROM insuranceproviders AS a, insurancetypes AS b, insurancevotes AS c
WHERE a.id = c.providerId
AND b.id = c.insTypeId

Upvotes: 0

Views: 94

Answers (3)

Jason Heo
Jason Heo

Reputation: 10236

following join query looks better:

SELECT a.providerName AS Assureur, b.insuranceType AS Produit,
    c.votedFor, c.votedFor2, c.email, c.comment, c.modified
FROM insuranceproviders AS a
    INNER JOIN insurancetypes AS b ON a.id = c.providerID
    INNER JOIN  insurancevotes AS c ON b.id = c.insTypeId

and You should add following INDEX:

ALTER TABLE insuranceproviders ADD INDEX(id);
ALTER TABLE insurancetypes ADD INDEX (providerID, insTypeId);
ALTER TABLE insurancevotes ADD INDEX(insTypeId);

Upvotes: 1

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16076

You can try like below:
[Here I am doing Left join as i dont know the table structure instead you can also have inner join]

SELECT a.providerName AS Assureur, b.insuranceType AS Produit, c.votedFor, c.votedFor2, c.email, c.comment, c.modified
FROM insurancevotes AS c
LEFT JOIN insuranceproviders AS a ON   a.id = c.providerId
LEFT JOIN insurancetypes AS b ON b.id = c.insTypeId

Upvotes: 1

Ismail Sahin
Ismail Sahin

Reputation: 2710

use indexes for a.id, c.providerId, b.id, c.id(if there is), c.insTypeId

Upvotes: 0

Related Questions