CODEWITHSUNDEEP
phpmysql
HazardousGlitch
HazardousGlitch

Reputation: 160

Speed up MySQL query when using ORDER BY and HAVING

I have a database that has a bunch of market orders for a game. My intent with the DB is to buy low and sell high so what I do is search for any product that I can make money off of. I enter a starting point and a end point and then check the DB for products that are for sale at the starting location and also check the end point for any buy orders that match and have a profit.

Right now I have about 750k listings and it takes about 8 seconds to process the query. It took less time when I had less rows. I will probably have over a 5 million entries once the database is full and I need to figure how how to speed up my query. I know when I take out the HAVING and ORDER BY, it takes about .25/s to query. Is there another way to do this in mySQL or should I find a way to do it in PHP? Any help would be appreciated!

Here is my query: 

    SELECT DISTINCT  
    f.orderID as fOrderID,
    f.typeID as fTypeId,
    f.solarSystemID as fSystemId,
    f.regionID as fRegionId,
    f.price as fPrice,
    f.volRemaining as fVolRemain,
    f.bid as fBid,
    f.reportedTime as fReportedTime,
    t.orderID as tOrderID,
    t.typeID as tTypeId, 
    t.solarSystemID as tSystemId,
    t.regionID as tRegionId,
    t.price as tPrice,
    t.volRemaining as tVolRemain,
    t.bid as tBid,
    if(((f.volRemaining < t.volRemaining)),
      ((f.volRemaining * t.price) - (f.price * f.volRemaining)), 
      ((t.volRemaining * t.Price) - (f.price * t.volRemaining))) as profit,
    t.reportedTime as tReporedtTime
    FROM marketData as f
    JOIN marketData as t on t.typeID = f.typeID 
    WHERE f.regionID = 10000001
    AND t.regionID = 10000030
    AND f.bid = 0
    AND t.bid = 1
    GROUP BY f.orderID
    HAVING profit > 1000000
    ORDER BY profit DESC
    LIMIT 100

MarketDB Layout(doesn't include 9 additional columns that I'm not using right now):

prim-unique                        indexed
+-------------------------------------------------------+
| orderID | regionID | stationID | typeID | bid | price |
+-------------------------------------------------------+ 
| 12345   |  223344  |  334455   |  13    |  0  | 22.43 |
| 12543   |  298474  |  348993   |  13    |  1  | 24.55 |   
| 24574   |  273646  |  392273   |  13    |  0  | 19.32 |
+-------------------------------------------------------+

EDITED: ADDED EXPLAIN:

id  select_type  table   type    possible_keys    key    key_len  ref              rows    Extra
1   SIMPLE       f       ALL     typeID           NULL   NULL     NULL             761338  Using where; Using temporary; Using filesort
1   SIMPLE       t       ref     typeID           typeID 8        market.f.typeID  76      Using where

Upvotes: 2

Views: 794

Answers (1)

jbo5112
jbo5112

Reputation: 833

It looks like the table should have 2 indexes. One index that includes (regionID,bid,typeID), probably in that order, and a second index that just has (typeID). The first index will be used to quickly scan f for records that match the criteria and retrieve the typeID (it's in the index). The second index, will be used on t for the join. I'm not sure why it doesn't use the larger index to include the where clause at the same time, but it could change with a later version of MySQL.

Depending on your actual data patterns, you might be able to tweak the first index a little more by creating multiple variations of it at the same time, that just try the 6 different possible orders, and run an explain plan to see which index is used. Then simply drop the unused indexes.

Note 1: Having the two indexes will slow down your inserts and deletes on the table, but I would expect the speed of this join query to be a bigger issue, especially without the indexes.

Note 2: Reducing the number of columns in the table will also help slightly, but does not address the scaling issue with a join.

Upvotes: 2

Related Questions