If you have less than approximately one hundred thousand trouble reports (entries in your equipments_* tables per month, than splitting the records into separate tables by month, or partititioning the tables, is definitely a bad idea. MySQL does just fine at handling tables containing dozens of millions of rows. Just fine. Seriously.
There are tens of thousands of successful applications in the world on modestly sized MySQL servers handling data sets of this size.
On the other hand, systems that employ partitioning require constant maintenance.
If your experience is to the contrary, it's because you haven't figured out how to use indexing and querying correctly. We can't tell from your question what kind of queries you are running in routine production, so it's not possible to give you clear advice about indexing. That being said, I guess it makes sense to put an index on (dateTime,id).
If you had one table rather than one per month as I suggest, you could do this to get your top ten equipment failures.
SELECT equipName
FROM equipments
GROUP BY equipName
ORDER BY COUNT(*) DESC
LIMIT 10
If you wanted the top ten failures for the 6 month period ending at the present time, you could use this query.
SELECT equipName
FROM equipments
WHERE dateTime >= NOW() - INTERVAL 6 MONTH
GROUP BY equipName
ORDER BY COUNT(*) DESC
LIMIT 10
This query would be made very efficient by a compound index on (dateTime, equipName) even for a dataset containing millions of rows spanning decades of time.
As it is, you have split your data into monthly tables. Here's how you can deal with that. First: use a sequence of UNION ALL operations to create a virtual table containing all the data. If all your monthly tables have the same columns in the same order, that's pretty easy if a little boring.
SELECT * FROM equipments_201404 UNION ALL
SELECT * FROM equipments_201403 UNION ALL
SELECT * FROM equipments_201402 UNION ALL
SELECT * FROM equipments_201401 UNION ALL
SELECT * FROM equipments_201312 UNION ALL
SELECT * FROM equipments_201311 UNION ALL
SELECT * FROM equipments_201310 UNION ALL
SELECT * FROM equipments_201309 UNION ALL
SELECT * FROM equipments_201308 UNION ALL
SELECT * FROM equipments_201307 UNION ALL
SELECT * FROM equipments_201306 UNION ALL
SELECT * FROM equipments_201305 UNION ALL
SELECT * FROM equipments_201304 /* etc etc you get the idea */
If you issue this query you'll get all your records as if they were in one table. Then you can use that as a subquery in the query shown above, as follows.
SELECT equipName
FROM (
SELECT * FROM equipments_201404 UNION ALL
SELECT * FROM equipments_201403 UNION ALL
SELECT * FROM equipments_201402 UNION ALL
SELECT * FROM equipments_201401 UNION ALL
SELECT * FROM equipments_201312 UNION ALL
SELECT * FROM equipments_201311 UNION ALL
SELECT * FROM equipments_201310 UNION ALL
SELECT * FROM equipments_201309 UNION ALL
SELECT * FROM equipments_201308 UNION ALL
SELECT * FROM equipments_201307 UNION ALL
SELECT * FROM equipments_201306 UNION ALL
SELECT * FROM equipments_201305 UNION ALL
SELECT * FROM equipments_201304 /* etc etc you get the idea */
) AS equipments
WHERE dateTime >= NOW() - INTERVAL 6 MONTH
GROUP BY equipName
ORDER BY COUNT(*) DESC
LIMIT 10
This lets you fake your main summary query into thinking it has one set of uniform data to process. Of course, indexes won't help much here.
Obviously, I have included too many monthly tables in the six-month query. You can fix that. But you'll need to fix it every month.
