[RESOLVED] Improving performance

mikawhat

I have the following query which collects data from 3 tables:
bettingoffer has about 40.000 entries,
event has about 1300 entries, and
outcome has about 40.000 entries

$q = "SELECT bettingoffer.odds FROM bettingoffer, outcome, event WHERE bettingoffer.outcomeId=outcome.object_id AND event.object_id=outcome.eventId AND outcome.eventId='".$m_id."' AND outcome.typeId='".$type_id."' AND outcome.integerParameter='".$t_id."' AND outcome.scopeId='".$scope_id."' ORDER BY bettingoffer.odds DESC LIMIT 1 ";

This will take around 10 seconds for providing odds for about 20 events (matches). I've tried running the query for all matches but i stopped it after 3 minutes with the page white and still loading, which means something is wrong...

What could i do in order to improve the time of the query execution?

mikawhat

The following is much better! Results displayed in 1.5 secs..

$q = "SELECT bettingoffer.odds FROM (event LEFT JOIN outcome ON event.object_id=outcome.eventId) LEFT JOIN bettingoffer ON bettingoffer.outcomeId=outcome.object_id WHERE outcome.eventId='".$m_id."' AND outcome.typeId='".$type_id."' AND outcome.integerParameter='".$t_id."' AND outcome.scopeId='".$scope_id."' ORDER BY bettingoffer.odds DESC LIMIT 1 ";

That should be fine, but id there's an even faster method, please advice.

dagon

index for:
outcome.eventId
outcome.typeId
outcome.integerParameter
outcome.scopeId

and
bettingoffer.odds

mikawhat

if i want to use the same query with a condition for date rage, should i also add index for date field?

Having index for many fields would be a problem? I mean, why not having index for all fields?

laserlight

mikawhat wrote:
Having index for many fields would be a problem? I mean, why not having index for all fields?

Because insertions and updates would then become slow.

mikawhat

And one last (hopefully) question... a primary key is already defined as index, or it has to be manually set like other fields?

Bjom

It should be afaik.

If you are unsure about what's happening: EXPLAIN is your friend. Check a query like so:

EXPLAIN SELECT myField1, myField2 FROM myTable JOIN myOtherTable USING (ID)

You'll get some info, including 'possible keys', indicating candidate indice for the query and 'keys' showing what index was really used.

Have a look at using explain to optimize queries

Bjom

dagon

PRIMARY KEY is a unique index

mikawhat

great! thank you all guys.