I am using the following MySQL query in a PHP script on a database that contains over 300,000,000 (yes, three hundred million) rows. I know that it is extremely resource intensive and it takes ages to run this one query. Does anyone know how I can either optimise the query or get the information in another way that’s quicker?
I need to be able to use any integer between 1 and 15 in place of the 14 in MID(). I also need to be able to match strings of lengths within the same range in the
games | longint, unsigned, Primary Key win | bit(1) loss | bit(1)
SELECT MID(`game`,14,1) AS `move`, COUNT(*) AS `games`, SUM(`win`) AS `wins`, SUM(`loss`) AS `losses` FROM `games` WHERE `game` LIKE '1112223334%' GROUP BY MID(`game`,1,14)
Thanks in advance for your help!
First, have an index on the game field…
The query seems simple and straightforward, but it hides that fact that a datasbase design change is probably required.
In such cases I always prefer to maintain a field that holds aggregated data, either per day, per user, or per any other axis. This way you can have a daily task that aggregates the relevant data and saves it in the database.
If indeed you call this query often, you should use the principle of decreasing the efficiency of insertion for increasing the efficiency of retrieval.