MySQL table slowing down

[AndyUK]

I have an audit table which has around 1.5 million rows and growing daily by around 100,000 entries. I found that when I query the table it’s a lot slower than it was yesterday, which was slower than the day before, and so on. I have a job which runs every 3 minutes and does a lot of queries against this table. The problem is now the job is taking longer than 3 minutes which causes a problem.

The job is basically doing this…

Check does an audit record exist within the last hour
If No > Insert an audit record
If Yes > Do nothing

Access to the audit data after this point is not speed critical, but the job of checking and inserting is.

Is there anyway to speed this up?

[r937]

[quote=“AndyUK, post:1, topic:208596, full:true”]Is there anyway to speed this up?
[/quote]
declare appropriate indexes

[AndyUK]

Already indexed

[r937]

is every query using index(es)? have you done an EXPLAIN?

[AndyUK]

I didn’t even know about explain

However it returned this…
(1, ‘SIMPLE’, ‘audit’, ‘ALL’, NULL, NULL, NULL, NULL, 1392086, ‘Using where’)

I think I am going to re-work my logic and have it select from a smaller table somewhere else.

[r937]

whatever indexes you created, it’s not using them

i would fix that before creating some other table