open All Channels
seplocked EVE Technology Lab
blankseplocked EVE Marketeer (beta) - (Market) Data Aggregation & Display
 
This thread is older than 90 days and has been locked due to inactivity.


 
Pages: [1] 2 3 4

Author Topic

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.23 00:02:00 - [1]
 

Edited by: Callean Drevus on 03/08/2011 20:16:28
Greetings,

I'm trying to build a next generation (market) information application, which will be aimed towards providing information people now lack. This means I'll be taking advantage of anything you tell me you need.

Current features (post will be updated):
- Allow you to upload data (sends the data to evemarketdata too)
- Query the market in a region, showing orders, history (graphs) and if an item with a blueprint, the components needed to build it (and their price).
- The application imports data from both EVE Marketdata and EVE Central, combining all data to have data from either website, or our own, whichever is more recent.
- An API to query that imported data.
- Allows you to send all evemail recieved by registered characters to your personal email account. (currently disabled)
- List of many other useful EVE applications (in the spirit of 3rdpartyeve.net, except more condensed)
- Use a marketscanner which makes certain all items are actually uploaded before moving on to new ones, and allows you to enter items you'd like to scan manually (including grouping).

Information (legacy):
- Shows how much mining a certain ore for an hour would make you in terms of ISK.

Take a look at: http://www.evemarketeer.com/

If you've got questions and or comments, I'd be very happy to hear them. I'll work towards incorporating any good suggestions into the site.

Callean Drevus

DominusBellus
Posted - 2011.07.23 09:23:00 - [2]
 

To get a bit more enthusiasm for your project, what benefits will your site be offering over and above what eve-marketdata currently provides? I assume you'll have more tools than just the lookup market prices that eve-marketdata gives?

Also, are you pulling data from eve-marketdata's api, or solely relying on your own uploader for market information?

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.23 09:32:00 - [3]
 

Edited by: Callean Drevus on 23/07/2011 09:33:46
Yes, there will be many things that I'm hoping to add that are currently not in eve-marketdata. There are already a few things which you can lookup on evemarketeer that are not visible on evemarketdata (including a cost estimate of an item were you to build it from scratch).

And yes, as a matter of fact, I pull the data from eve-marketdata every day at the moment. All updates done with my uploader as sent to both eve-marketdata and evemarketeer.

Max Kolonko
Caldari
Worm Nation
Ash Alliance
Posted - 2011.07.23 10:27:00 - [4]
 

I like that you send data also to eve-marketdata

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.23 11:06:00 - [5]
 

Edited by: Callean Drevus on 23/07/2011 11:17:34
Originally by: Max Kolonko
I like that you send data also to eve-marketdata


I'm using his data, it's only fair I return the favour.

I tried sending the data to eve-central too, but their upload tool/format is just too convoluted.

EDIT: One thing I will try to do better than eve-marketdata.com, for example, is I will try to generate less internal server errors during downtime.

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.24 22:30:00 - [6]
 

Something which has absolutely nothing to do with any market data whatsoever, but I thought was needed anyway.

I've compiled a list of tools that everybody probably thinks are handy to have, but may not have yet heard about. I'll be working to keep that list up to date with everything new that might come out.

It can be found on: http://www.evemarketeer.com/home/tools

Hope you enjoy it. If you see your tool in the list, and wish to submit better information (logo, description, or if there's just something wrong), by all means, go ahead. Everything that's on the website now was simply taken from where I could find it, with the result that many applications do not have a logo, or an ugly one, or a rather ugly description.

Domi Etymology
Posted - 2011.07.26 03:13:00 - [7]
 

currently using your upload client, and it's using the upload ID that it seems to remember from the eve-marketdata client. Can you make it so you can edit the upload key on your page, so we can use the same key we use for eve market data, or alternatively, edit the upload client so it can accept one key for eve-marketdata and one key for evemarketeer?

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.27 20:00:00 - [8]
 

Originally by: Domi Etymology
currently using your upload client, and it's using the upload ID that it seems to remember from the eve-marketdata client. Can you make it so you can edit the upload key on your page, so we can use the same key we use for eve market data, or alternatively, edit the upload client so it can accept one key for eve-marketdata and one key for evemarketeer?


I'd been trying to make my upload key the same as the one used for eve-marketdata, but after contacting its creator found out that it is impossible to duplicate it, as it's a unique key related to his database.
Still, simply having two different fields is a pretty clean and simple idea. I'll get working on this.

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.27 21:33:00 - [9]
 

Edited by: Callean Drevus on 27/07/2011 21:35:05
Okey, the uploader has been updated, you are now able to enter both an EVE Marketeer upload key, and a EVE Marketdata upload ID seperately from another. I've also changed the header to be more in line with the application that the uploader is used for.
Possible future upgrades are making it possible to specify an unlimited number of endpoints/upload keys.

I'm also happy to state that EVE Marketeer is now aggregating data from our own uploads, EVE Marketdata and EVE Central, about as realtime as I can make it. The only big thing missing now is an API that allows you to query all that data at once. But don't worry, that's coming soon ;)

Do you guys have any ideas of what should ABSOLUTELY be in the API? Or something you are missing somewhere else right now?

Domi Etymology
Posted - 2011.07.28 00:10:00 - [10]
 

Could I suggest you put a download link to your uploader somewhere on the front page. Either I'm utterly blind, or it's currently a real pain in the butt trying to find it!

Domi Etymology
Posted - 2011.07.28 00:22:00 - [11]
 

Just downloaded the uploader from http://www.evemarketeer.com/downloads/latest_client.zip

it's giving me NEMIA Uploader version 2.4EM

Is this the new uploader? If so.... where do you put in the 2nd new key for evemarketeer?

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.28 11:19:00 - [12]
 

Edited by: Callean Drevus on 28/07/2011 11:54:25
I'm sorry, you are correct in your assumption that something went wrong. I'm working on fixing that now (I logically already had the new uploader on my PC, so it hadn't occurred to me yet to try to download it and see if it works).

UPDATE: An update has been posted to the website with a button for the downloader on the main page, and the new version of the uploader is now truly online.

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.28 14:31:00 - [13]
 

Edited by: Callean Drevus on 28/07/2011 14:31:32
I'm happy to announce we now also provide an API for retrieving all the consolidated data from EVE Central, EVE Marketdata and our own uploads (only aggregated data/statistics for now). I calculate the following stats for all items in every region (and a specific set for the Jita station, which can be retrieved with a unique call):

type_id : the type id for which this data applies
region_id : the region for which this data applies
datetime : the time this data was generated
sell_lowest : the lowest price this item is sold for
sell_avg : the (weighted) average price this item is sold for
sell_geo_mean : the geometric mean of the price this item is sold for
sell_harm_mean : the harmonious mean of the price this item is sold for
sell_highest : the highest price this item is sold for
sell_lowest5 : the average price of the lowest 5% of the sell market
sell_orders : the amount of sell orders
supply : the total amount of items available
sell_worth : the cost of buying the entire sell market
buy_highest : the highest price this item is bought for
buy_avg : the average price this item is bought for
buy_geo_mean : the geometric mean of the price this item is bought for
buy_harm_mean : the harmonious mean of the price this item is bought for
buy_lowest : the lowest price this item is bought for
buy_highest5 : the average price of the top 5% of the buy market
buy_orders : the amount of buy orders
demand : the total amount of items bought
buy_worth : the profits of selling to the entire buy market

Go to http://www.evemarketeer.com/api to make use of this service.

Azazel Mordred
Minmatar
Cloak of Shadows
Posted - 2011.07.28 17:06:00 - [14]
 

Wow, your API looks really neat. Just the type of thing I've been looking for :). It's also fantastic that it's combining both Eve Central and Eve Marketdata in an easy to consume format.

I think this would make an excellent replacement for OOE's current market lookup (EVE-Central only).

Thanks!

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.29 22:53:00 - [15]
 

Thank you. I'm happy to provide.

That said, I've added a trade route finder. If everything works correctly, it also filters out the idiotic/unrealistic orders. The only problem for now is it's exceptionally slow and locks up most of the server when it's calculating, but that will be fixed later :P just don't all start using it at the same time now.

Domi Etymology
Posted - 2011.07.30 01:40:00 - [16]
 

Something I've noticed, if you put in a partial item name into the search, ie: "trit" and then click on the "Tritanium" link from the dropdown, it fills the search box with Tritanium, but actually sends you to http://www.evemarketeer.com/item/itemprice/trit and therefore you get no results, since there is no "trit" item.

hope that made sense :P

That's using firefox, btw. Haven't tested in IE/chrome

Almir Kadric
Posted - 2011.07.30 10:59:00 - [17]
 

It's quite a funny thing actually, I've also been developing a similar tool but more focused around manufacturing to start. I've just about finished the manufacturing tool. So far I've spent a few days making my site.HTTP://EVECQ.COM

Manufacturing tool should be up either tomorrow or day after, just adding last pieces to it.

And what makes it funny is that since I've started (my tool started as a spreadsheet a few weeks ago till corpie asked for share, so I started web tool) is that I've noticed a lot of web tools popping up on the forums lately. Competition is going to be tough XD

Almir Kadric
Posted - 2011.07.31 09:21:00 - [18]
 

Well I notice someone looked at my site after I posted it here, so to do myself some justice (though the website is far from complete) I've put up the new manufacturing tool. Take another look. Will start my own thread somewhere down the track once I'm happy with the volume of content and features.

SORRY TO THE OWNER OF THIS THREAD FOR SLIGHTLY HI-JACKING HIS THREAD XD

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.31 10:04:00 - [19]
 

Edited by: Callean Drevus on 31/07/2011 10:09:53
@Domi: That should be fixed now. It's using the entire name for autocomplete too.

Ha! This is a free bump! I do not mind at all. It's quite funny to see how we all though of the same things (more or less) and started building it. I must say your manufacturing tool is exactly what I had in mind but were not able to build yet because I was focused on different things.

At least that website looks decent though! I'm sorry to say but you've given me some good ideas that I might steal :P alternatively, I'm starting to wonder whether it might not be better to work together on something epic instead of work parallel to eachother making everything that much slower to develop, but I also guess every one of us like his own baby. I for one wouldn't give up the domain name evemarketeer.com

Aside from that, your calculate button should be moved, and redesigned, I had trouble spotting it :P same for the ME and PE input fields. Am I correct in my assumption that it takes the most efficient numbers automatically? Oh, and you need a totals row for the materials cost (even though it's already in the top table.
I'm also a bit confused as to what the difference between market and database is, since there seems to be none. Anyway, great website!

As for myself. 've been trying to see whether my trade route calculation fails to be fast because I am using MySQL (eve central does the same joins, but ends up incredibly faster than my website on PostgreSQL). If so, I'll probably be trying to port the entire website over to Postgres, since whatever I'm calculating I'm doing incredible amounts of joins (and inserts), and it seems postgre is good at that.

Almir Kadric
Posted - 2011.07.31 14:45:00 - [20]
 

Originally by: Callean Drevus
Ha! This is a free bump! I do not mind at all. It's quite funny to see how we all though of the same things (more or less) and started building it. I must say your manufacturing tool is exactly what I had in mind but were not able to build yet because I was focused on different things.


Good to hear XD

Originally by: Callean Drevus
At least that website looks decent though! I'm sorry to say but you've given me some good ideas that I might steal :P alternatively, I'm starting to wonder whether it might not be better to work together on something epic instead of work parallel to eachother making everything that much slower to develop, but I also guess every one of us like his own baby. I for one wouldn't give up the domain name evemarketeer.com


I agree on both points, the dilemma =S
I'm the same with evecq.com such a nice simple name, yet capitalizes on eve's newest feature, surprised it was available XD

Originally by: Callean Drevus
Aside from that, your calculate button should be moved, and redesigned, I had trouble spotting it :P same for the ME and PE input fields. Am I correct in my assumption that it takes the most efficient numbers automatically? Oh, and you need a totals row for the materials cost (even though it's already in the top table.
I'm also a bit confused as to what the difference between market and database is, since there seems to be none. Anyway, great website!


Thanks for the feedback! To be honest i haven't put too much thought in the visual look. Just roughly did how it should look.

The ME and PE auto fill is a little better than just the most efficient numbers, it does 2 things. Calculates perfect ME/PE and then calculates ME/PE after 1 month of training and then picks the lower number. This way I don't get unrealistic ME/PE when i do my mass calculation caching.

Originally by: Callean Drevus
As for myself. 've been trying to see whether my trade route calculation fails to be fast because I am using MySQL (eve central does the same joins, but ends up incredibly faster than my website on PostgreSQL). If so, I'll probably be trying to port the entire website over to Postgres, since whatever I'm calculating I'm doing incredible amounts of joins (and inserts), and it seems postgre is good at that.


Since you helped me, I'll give you something to help ;p

Don't bother migrating to posgres. I also did some crazy queries which too up to 10 minutes to complete! then I got them down to less than 100ms!
(a page which did my special production calculations on every item in the game)

There's a few things to consider:
1. Indexes! (you need to index appropriate fields to help boost find times)
2. Caching IS GOLD (create new caching tables which map all possible options on the fly so you don't redo the same calculations over and over again)
3. Revise your SQL statements to be more efficient, use ANALYZE or just manually chunk out parts of the Joins and see what the difference in time is, once you locate your offending statement, optimize it.

If you need more help, shoot it my way. And I'm sure we can work together in some parts, since you're focusing on market aggregation (or so I'm assuming). Whilst I'm focusing on everything else where market aggregation is just but a small part. I'm sure if there's any sections of my site you like (such as my very complicated manufacturing tool or profit loss calculations on large scales) I'm sure I could provide you with an API embed you can use rather than writing all that grueling code yourself, not to mention wasted server CPU ;p

NOW OFF TO IMPLEMENT FEEDBACK PAGES along with your recommendations ;p
Just got my news feed up. Thankful I finally got some more content on the home page
Was looking quite sad =(

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.07.31 19:14:00 - [21]
 

Edited by: Callean Drevus on 31/07/2011 20:23:31
Originally by: Almir Kadric
Since you helped me, I'll give you something to help ;p

Don't bother migrating to posgres. I also did some crazy queries which too up to 10 minutes to complete! then I got them down to less than 100ms!
(a page which did my special production calculations on every item in the game)

There's a few things to consider:
1. Indexes! (you need to index appropriate fields to help boost find times)
2. Caching IS GOLD (create new caching tables which map all possible options on the fly so you don't redo the same calculations over and over again)
3. Revise your SQL statements to be more efficient, use ANALYZE or just manually chunk out parts of the Joins and see what the difference in time is, once you locate your offending statement, optimize it.

If you need more help, shoot it my way. And I'm sure we can work together in some parts, since you're focusing on market aggregation (or so I'm assuming). Whilst I'm focusing on everything else where market aggregation is just but a small part. I'm sure if there's any sections of my site you like (such as my very complicated manufacturing tool or profit loss calculations on large scales) I'm sure I could provide you with an API embed you can use rather than writing all that grueling code yourself, not to mention wasted server CPU ;p


The problem here is not so much in the way the queries are optimized (though I have to admit, with less joins, it works better ;)) and there's certainly optimization and pre-calculation possible for some of the things (if not a lot), but the most important part, matching sell orders against buy orders is just an insanely resource espensive part, even without all the other joins it takes ages to complete. All optimization I could throw at it didn't help much. I did manage to half the time needed by neglecting to sort though (doing that in PHP afterwards proved quite much faster than doing it in MySQL, which is pretty stupid).

There are probably some ways in which the query can be made better, but to be honest, I'm just interested in seeing how a different db turns out :) I won't be moving the site itself over until I see that the queries are actually running better.

UPDATE: There is a DISTINCT time advantage in using PostgreSQL. That is after spending a day trying to optimize MySQL as best as possible, then simply copying the database over into a default PostgreSQL install and running the query. That which didn't even complete within 2 minutes on MySQL takes about 15 seconds now. The only problem is migrating the entire codebase of the website :S

Wind Jammer
Minmatar
Molden Heath Software Company
Posted - 2011.07.31 21:30:00 - [22]
 

Edited by: Wind Jammer on 31/07/2011 21:31:44
Originally by: Callean Drevus

The problem here is not so much in the way the queries are optimized (though I have to admit, with less joins, it works better ;)) and there's certainly optimization and pre-calculation possible for some of the things (if not a lot), but the most important part, matching sell orders against buy orders is just an insanely resource espensive part, even without all the other joins it takes ages to complete. All optimization I could throw at it didn't help much. I did manage to half the time needed by neglecting to sort though (doing that in PHP afterwards proved quite much faster than doing it in MySQL, which is pretty stupid).

There are probably some ways in which the query can be made better, but to be honest, I'm just interested in seeing how a different db turns out :) I won't be moving the site itself over until I see that the queries are actually running better.

UPDATE: There is a DISTINCT time advantage in using PostgreSQL. That is after spending a day trying to optimize MySQL as best as possible, then simply copying the database over into a default PostgreSQL install and running the query. That which didn't even complete within 2 minutes on MySQL takes about 15 seconds now. The only problem is migrating the entire codebase of the website :S



The best advice I can give you is to slice and dice the data you access online. The first incarnation of the EVE Production Mixer used the static dump as-is in a JavaDB database, and the SQL queries were pretty slow. I wrote an extract that created highly-prepared tables with had the data in a way that was convenient.

Now the Prod Mixer is hosted on Google App Engine which only supports a NoSQL datastore, and it's faster still :) If you do Java or Python check it out the GAE.


-edit
Smart looking site btw :)

Almir Kadric
Posted - 2011.08.01 00:45:00 - [23]
 

Originally by: Callean Drevus
UPDATE: There is a DISTINCT time advantage in using PostgreSQL. That is after spending a day trying to optimize MySQL as best as possible, then simply copying the database over into a default PostgreSQL install and running the query. That which didn't even complete within 2 minutes on MySQL takes about 15 seconds now. The only problem is migrating the entire codebase of the website :S


To be honest the differences between posgres sql and mysql these days shouldn't be too big, if anything maybe a second or so. And this really sounds like a optimization issue to me. Mind showing me your SQL statement and letting me play with it? If I get it running smoothly on my DB it could save you a day or few hours (depending on what framework you're using).

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.08.01 06:57:00 - [24]
 

Edited by: Callean Drevus on 01/08/2011 10:43:51
Originally by: Almir Kadric
To be honest the differences between posgres sql and mysql these days shouldn't be too big, if anything maybe a second or so. And this really sounds like a optimization issue to me. Mind showing me your SQL statement and letting me play with it? If I get it running smoothly on my DB it could save you a day or few hours (depending on what framework you're using).


You are welcome to look at them. The easiest way to get the tables to try out this query is getting them from eve-marketdata.com, they provide a dump of all the buy/sell orders in all regions, and they're the most important tables to use for this query.

This is what I'm doing (in basis, the true query has more joins, but those are not tables easily reproduced :) SolarSystemDistances contains a table with distances between all solar systems. You can remove it without having too big an impact on the overal time taken.
All fields which are mentioned in joins &| where have been indexed (though they have all been indexed separately, would it be better if they formed a single index?).

SELECT 
ise.type_id,
ise.qty_avail AS from_qty,
ibu.qty_avail AS to_qty,
IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail) AS transferrable,
ise.price as sell_price,
ibu.price as buy_price,
FORMAT((ibu.price - ise.price) * IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail),0) as total_profits,
FORMAT((ibu.price - ise.price) * IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail) / ssd.Distance,0) as profit_per_jump,
ssd.Distance
FROM
items_selling ise
INNER JOIN
items_buying ibu ON
ibu.station_id = 60003760 AND
ise.type_id = ibu.type_id AND
ise.qty_min <> ise.qty_avail
INNER JOIN
SolarSystemDistances ssd ON
ssd.FromSolarSystemID = ise.solarsystem_id AND
ssd.ToSolarSystemID = ibu.solarsystem_id
WHERE
ise.date_created > NOW() - INTERVAL 8 HOUR
ise.station_id = 60008494 AND
ise.price < ibu.price
LIMIT 20;


If you can change this query in any way to make it useful, I'd be very much obliged.

Ila Minn
Posted - 2011.08.01 08:44:00 - [25]
 

First of all: Thanks for providing service to the community.

I very much like the concept of how you both aggregate and share data, and would very much like to make more use of the data you provide. Sadly, you don't seem to provide any method to download orders for items in regions, which makes it hard to evaluate two things that I would be very keen on knowing:

- The highest buy/lowest sell price of an item in a station other than Jita.
- The number of units of an item available _close_ to the highest buy/lowest sell price for a given station (like, within one or two percent of the price).

Maybe you could extend the market aggregation data to at least contain the second information for the Jita prices, that in itself would already make me a happy minmatar.

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.08.01 10:39:00 - [26]
 

Edited by: Callean Drevus on 01/08/2011 10:41:24
Originally by: Ila Minn
First of all: Thanks for providing service to the community.

I very much like the concept of how you both aggregate and share data, and would very much like to make more use of the data you provide. Sadly, you don't seem to provide any method to download orders for items in regions, which makes it hard to evaluate two things that I would be very keen on knowing:

- The highest buy/lowest sell price of an item in a station other than Jita.
- The number of units of an item available _close_ to the highest buy/lowest sell price for a given station (like, within one or two percent of the price).

Maybe you could extend the market aggregation data to at least contain the second information for the Jita prices, that in itself would already make me a happy minmatar.



Since making happy minmatars is one of the goals of this operation, I've added the ability to do both. You can query aggregated data for any station now (calculated on the fly, so you're always sure I'm using the most recent orders) and I've added 2 new fields to the data which show the amount of items available within 5% of the lowest sell/highest buy price. Since these are calculated when orders are uploaded (or requested via the new API call), it might take a bit before it's propagated across all items/regions.

I'm sorry for the location of the fields (in the middle of the data, meaning if you already did something with CSV parsing it would now be messed up), but fields are exported in the same order as they are in my database, and I have to prevent myself from going insane. After another week or so I'll make a distinct API version that will not change afterwards anymore (can continue to be used without any significant changes except bugfixes).

Almir Kadric
Posted - 2011.08.02 04:43:00 - [27]
 

Edited by: Almir Kadric on 02/08/2011 04:56:24
Originally by: Callean Drevus
Edited by: Callean Drevus on 01/08/2011 10:43:51
Originally by: Almir Kadric
To be honest the differences between posgres sql and mysql these days shouldn't be too big, if anything maybe a second or so. And this really sounds like a optimization issue to me. Mind showing me your SQL statement and letting me play with it? If I get it running smoothly on my DB it could save you a day or few hours (depending on what framework you're using).


You are welcome to look at them. The easiest way to get the tables to try out this query is getting them from eve-marketdata.com, they provide a dump of all the buy/sell orders in all regions, and they're the most important tables to use for this query.

This is what I'm doing (in basis, the true query has more joins, but those are not tables easily reproduced :) SolarSystemDistances contains a table with distances between all solar systems. You can remove it without having too big an impact on the overal time taken.
All fields which are mentioned in joins &| where have been indexed (though they have all been indexed separately, would it be better if they formed a single index?).

SELECT 
ise.type_id,
ise.qty_avail AS from_qty,
ibu.qty_avail AS to_qty,
IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail) AS transferrable,
ise.price as sell_price,
ibu.price as buy_price,
FORMAT((ibu.price - ise.price) * IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail),0) as total_profits,
FORMAT((ibu.price - ise.price) * IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail) / ssd.Distance,0) as profit_per_jump,
ssd.Distance
FROM
items_selling ise
INNER JOIN
items_buying ibu ON
ibu.station_id = 60003760 AND
ise.type_id = ibu.type_id AND
ise.qty_min <> ise.qty_avail
INNER JOIN
SolarSystemDistances ssd ON
ssd.FromSolarSystemID = ise.solarsystem_id AND
ssd.ToSolarSystemID = ibu.solarsystem_id
WHERE
ise.date_created > NOW() - INTERVAL 8 HOUR
ise.station_id = 60008494 AND
ise.price < ibu.price
LIMIT 20;


If you can change this query in any way to make it useful, I'd be very much obliged.


You're such an ASS XD
LOL your problem drove me a little nuts for the wrong reasons.
Let me explain ;p
I first wrote a piece of SQL to get the shortest route between 2 systems and the number of jumps HOWEVER as we both know the math in that is insane and it was slow. So then i began to engineer a caching system shortest jump distances, but the permutations are insane. So i went a step further and created a script which does it in the background, without overlaps increasing the speed quite nicely and fixing all bugs and glitches.

SO with the cache table being available to me I ran your SQL and found it's problem, only took me a few seconds lol.

the problem is the 2 following statements:
1. INNER JOIN items_buying ibu ON
2. ise.price < ibu.price

The first obviously since it's trying to match all the data across an insane amount of rows.

The second is actually a funny point because it's a math check, which you'd think is fast, however in a database select algorithm it's extremely CPU intensive and time consuming, since it checks every row of data this also applies to the above comment.

NOW THE IRONY, it's actually faster to sort the data (KEY POINT). Since it just has to bubble sort it as it spits it out.

THUS THE NEW QUERY....WILL CONTINUE ON NEXT POST (DAMN CHARACTER LIMIT)

Almir Kadric
Posted - 2011.08.02 04:48:00 - [28]
 

Edited by: Almir Kadric on 02/08/2011 04:52:20
Edited by: Almir Kadric on 02/08/2011 04:51:11
Edited by: Almir Kadric on 02/08/2011 04:50:41
Edited by: Almir Kadric on 02/08/2011 04:49:48
Originally by: Almir Kadric
THUS THE NEW QUERY....WILL CONTINUE ON NEXT POST (DAMN CHARACTER LIMIT)


Stupid 2 minutes between posts time limit as well =_=

HERE IT IS:



SELECT
ise.type_id,
ise.qty_avail AS from_qty,
ibu.qty_avail AS to_qty,
IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail) AS transferrable,
ise.price as sell_price,
ibu.price as buy_price,
FORMAT((ibu.price - ise.price) * IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail),0) as total_profits,
FORMAT((ibu.price - ise.price) * IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail) / ssd.jumps,0) as profit_per_jump,
ssd.jumps,
ise.date_created
FROM `eve-marketdata_items_selling` AS ise
INNER JOIN `eve-marketdata_items_buying` AS ibu ON
ibu.type_id = ise.type_id
LEFT JOIN `eve_jumpsbetweensystems` ssd ON
ssd.fromSystemID = ise.solarsystem_id AND
ssd.toSystemID = ibu.solarsystem_id
WHERE
ise.station_id = 60008494 AND
ibu.station_id = 60003760 AND
ise.qty_min <> ise.qty_avail AND
ise.date_created > (NOW() - INTERVAL 55 HOUR)
ORDER BY profit_per_jump DESC
LIMIT 20


EXECUTES IN .218 seconds ^_^

Now I could give you the exact math as to why this happens (algorithm execution time math by instruction order) but I honestly CBF opening my OLD uni text book to look it up, and it's been a while since I did the math so I can't remember it (something like ln(n) per loop instruction blah blah)

UPDATE: sorry for mass updates, didn't know the right code tag XD

Callean Drevus
Caldari
Icosahedron Crafts and Shipping
Silent Infinity
Posted - 2011.08.02 10:35:00 - [29]
 

Edited by: Callean Drevus on 02/08/2011 11:02:53
Edited by: Callean Drevus on 02/08/2011 10:41:03
Originally by: Almir Kadric
EXECUTES IN .218 seconds ^_^

Now I could give you the exact math as to why this happens (algorithm execution time math by instruction order) but I honestly CBF opening my OLD uni text book to look it up, and it's been a while since I did the math so I can't remember it (something like ln(n) per loop instruction blah blah)

UPDATE: sorry for mass updates, didn't know the right code tag XD


You are my hero!

Though it still takes about 8 seconds+ to finish the query, at least it actually finishes within a reasonable amount of time now. After caching it's fast indeed. If it's fast on your first query I'm probably going to have to do some optimization to my MySQL server ;)

I don't think I'd have thought about the price < price statement being the issue... As you said, math should be fast, even for lots of rows (you're doing that math too if you're sorting) still, I'll keep this one in mind in case something like this ever happens again.

Also, I'm sorry for not providing the SolarSystemDistances table, if I could have remembered exactly WHERE I had found it I would happily have provided...

In addition. I had to change the ORDER BY clause back to
(ibu.price - ise.price) * IF(ise.qty_avail > ibu.qty_avail, ibu.qty_avail, ise.qty_avail) / ssd.Distance

otherwise it wouldn't sort anything above 999, leading to some pretty idiotic trades appearing :P

Almir Kadric
Posted - 2011.08.02 10:54:00 - [30]
 

Edited by: Almir Kadric on 02/08/2011 10:55:07
Originally by: Callean Drevus

You are my hero!

Though it still takes about 8 seconds+ to finish the query, at least it actually finishes within a reasonable amount of time now. After caching it's fast indeed. If it's fast on your first query I'm probably going to have to do some optimization to my MySQL server ;)



Not sure if it's fast on my first query, but since you should only realistically have 1 first query every hour at most (once a day for me since im using static dumps at the moment) I don't see it as that much of an issue. One user takes the **** for everyone else's benefit

Originally by: Callean Drevus

I don't think I'd have thought about the price < price statement being the issue... As you said, math should be fast, even for lots of rows (you're doing that math too if you're sorting) still, I'll keep this one in mind in case something like this ever happens again.



Well I could tell from one look and let me explain why, it should help you understand DB's better. When data is stored in a DB it's stored within a type of tree which is sorted by the primary keys of that table. And in certain cases when you reach a leaf that leaf could be a bucket of data which is once again sorted. So when you traverse a database tree, to get ordered items is easy as it just traverses the tree differently and worst case scenario it would have to sort the smaller bucket. BUT if you take math logic into the picture which a DB isn't sorted by it has to execute the math then resort the entire resultant set. Now this is purely hypothetical and situation based (DB type, engine etc). BUT the moral here is avoid anything that isn't already in the db as a key or resultant set, unless the set you get back is quite small.

Originally by: Callean Drevus

Also, I'm sorry for not providing the SolarSystemDistances table, if I could have remembered exactly WHERE I had found it I would happily have provided...


So you actually got someone elses data for this XD, I looked for it and couldn't find it. I'm slowly rebuilding my own set atm, if you do find where you got it from would appreciate it heaps. Otherwise I'll quietly work on a new piece of code which also can detect defects in shortest paths when i have to update the EVE Static Datadump as the mapsolarsystemjumps table could change. You gave me quite a side project to accomplish XD

Let me know if you have any other issues ^_^


Pages: [1] 2 3 4

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only