CODEWITHSUNDEEP
mysqlsql
tuscan88
tuscan88

Reputation: 5829

How to increase performance of MySQL query

I have a database that holds the details of various different events and all of the odds that bookmakers are offering on those events. I have the following query which I am using to get the best odds for each different type of bet for each event:

SELECT  
    eo1.id, 
    eo1.event_id, 
    eo1.market_id, 
    IF(markets.display_name IS NULL, markets.name, markets.display_name) AS market_name, 
    IF(market_values.display_name IS NULL, market_values.name, market_values.display_name) AS market_value_name, 
    eo2.bookmaker_id, 
    eo2.value
FROM event_odds AS eo1
JOIN markets ON eo1.market_id = markets.id AND markets.enabled = 1
JOIN market_values on eo1.market_value_id = market_values.id
JOIN bookmakers on eo1.bookmaker_id = bookmakers.id AND bookmakers.enabled = 1
JOIN event_odds AS eo2 
ON 
    eo1.event_id = eo2.event_id 
    AND eo1.market_id = eo2.market_id 
    AND eo1.market_value_id = eo2.market_value_id 
    AND eo2.value = (
        SELECT MAX(value) 
        FROM event_odds 
        WHERE event_odds.event_id = eo1.event_id 
        AND event_odds.market_id = eo1.market_id 
        AND event_odds.market_value_id = eo1.market_value_id
    )
WHERE eo1.`event_id` = 6708
AND markets.name != '-'
GROUP BY eo1.market_id, eo1.market_value_id
ORDER BY markets.sort_order, market_name, market_values.id

This returns exactly what I want however since the database has grown in size it's started to be very slow. I currently have just over 500,000 records in the event odds table and the query takes almost 2 minutes to run. The hardware is decent spec, all of the columns are indexed correctly and the table engine being used is MyISAM for all tables. How can I optimise this query so it runs quicker?

Upvotes: 1

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

For this query, you want to be sure you have an index on event_odds(event_id, market_id, market_value_id, value).

In addition, you want indexes on:

  • markets(id, enabled, name)
  • bookmakers(id, enabled)

Note that composite indexes are quite different from multiple indexes with one column each.

Upvotes: 3

user6778410
user6778410

Reputation:

Create a MySQL view for this SQL. Try to fetch data from that MySQL view then. This would help in increasing the speed and can reduce complexity. Try pagination for listing using limit. This will also reduce the load on server. Try to indexes for typical columns

Upvotes: 0

Related Questions