LMeve dev blog: tuning SQL performance and EVE SSO

Hello industry moguls and third party devs of EVE o7

First off, I’d like to say I respect Rixx Javix greatly for his last post about EVE industrialists and his respect towards them. Much appreciated, dear sir! But today’s dev blog post will be about three other things. First is a solution to the performance problems that Aideron Technologies instance of LMeve was having recently. Second is an announcement of a new feature, which thanks to @CCP_FoxFour will soon become widely available: ability to log in to LMeve using your EVE Online credentials (EVE SSO). Last but not least, I’ve added the new Blueprints.xml API endpoint.

Ohnoes, LMeve slows down!

I’ve got numerous complaints from my corp members recently about LMeve being slow and unresponsive. It was apparent on many subpages, so at first it was hard to pinpoint. Our database has grown over the past two years, and I thought this performance hit was caused by the amount of records we have accumulated:

When you look at it, there is a few hundred thousands records there. But usually we only need to display the current month, and the rest has to be filtered out:

This prompted me to have a look at how I filter out data by dates to show the current month only:

SELECT (...) FROM (...)
WHERE date_format(beginProductionTime, '%Y%m') = '${year}${month}'
AND (...);

At first glance it looks completely logical (and it actually works). I know the current month and year, so I only select records that have their year and month equal to the current one. But look closely what I’ve done there. I convert a DATETIME field “beginProductionTime” to a string which shows year and month in YYYYMM format, and then I compare this string with another string. As you probably know, comparing strings is much slower than comparing numbers (it’s why in databases you use indexes on text fields that need comparison). But I made it even worse; besides comparing strings, I converted DATETIME, and I did that for each and every record encountered by the query!

Here’s a piece of advice when you write an SQL query that sifts through a lot of data:

• don’t run unnecessary functions or conversions on database fields that you use in WHERE clause
• create indexes on TEXT and VARCHAR fields if you have to compare them

Each function which you put in there will have to execute for every record in the table, which will adversely affect performance.

When you take the two rules above into account, fixing my queries was pretty simple and straightforward. Instead of converting DATETIME values to string, I converted the compared string into a DATETIME:

SELECT (...) FROM (...)
WHERE beginProductionTime BETWEEN '${year}-${month}-01' AND LAST_DAY('${year}-${month}-01')
AND (...);

See? The values I compare beginProductionTime against are only evaluated once, instead of for every record, which means the whole SQL executes way faster than before.

Results:

Old query execution time: 37.4 seconds

New query execution time: 0.87 seconds

Op success! o7

Damn, I forgot my LMeve password again

Well, this kind of excuse will not be possible anymore, because thanks to @CCP_FoxFour and the limited EVE SSO trial, LMeve can now benefit from the SSO mechanism. CCP FoxFour explained SSO in detail in his dev blog here. How does this work?

• LMeve redirects user to SSO at https://login.eveonline.com/
• User logs in with his EVE Online login details and chooses one of the characters
• EVE SSO redirects user back to LMeve
• LMeve confirms with the SSO server that the login is valid
• LMeve accesses the character name and ID and checks if that character is allowed to use LMeve (character must belong to the corporation configured in LMeve, and that character must also be linked to an LMeve account on “Characters” page. Users can do it on their own using their API keys.)

This is of course simplified exchange, because special token and secret values are also exchanged to make sure that nothing got spoofed on the way.

SSO feature for LMeve has already been developed and tested, but to set it up in your own instance of LMeve, you will need a special “client_id” and “secret” values. These values are similar to API keys, and you will be able to generate them  on the new 3rd party developer page (beta of this page working with Singularity server is already available).

New API endpoint

The last feature is the addition of Blueprints.xml API endpoint, which returns data about all the blueprints owned by a corporation, both originals and copies. Information retrieved from it contains ME and TE levels. Previously LMeve users had to input ME and TE manually for each blueprint owned by the corporation. With this new endpoint LMeve does all this automatically. This way all cost predictions made by LMeve always use up to date ME levels.

That’s all for today!

You can download the latest LMeve version from GitHub.