If IP Caches is enabled (and it should be if any of the external spamlists are set to be checked (you don't know how slow these sites are!)), then
avhfdas_ipcache is the
most important table in AVH FDAS. As such, I'd like to present to you some of my ideas:
- Drop the added and lastseen indexes, because they are used only ONCE A DAY, but get updated (HUNDRED OF) THOUSANDS OF TIMES per day. The overhead delay they cause during INSERTs is unjustified. If you drop these indexes, avhfdas_ipcache will also need much less disk space. No change in your plugin code is required.
- Set the lastseen column to ON UPDATE CURRENT_TIMESTAMP. You will then spare some plugin code and let MySQL do its thing faster.
- Drop the added column. Does the plugin use it? I can't find it used anywhere. If you don't want to drop it...., then set the added column to DEFAULT CURRENT_TIMESTAMP. You will then spare some plugin code and let MySQL do its thing faster.
- I have an analogy. Think of the 3rd party spamlists as the hard drive of a computer. Think of the IP Cache table as its RAM. Do you know what we need, Peter? A CPU Cache (L1, L2, L3 cache, etc.), of course.
Store the recent ham visitor IPs in a serialized array saved through the WordPress Transients API. Yes, create a temporary, first-in-first-out, whitelist. The max_size of this array is up to you! Subsequent pageviews of ham visitors (and I hope they prolong their visit!) won't even require a MySQL query. - Finally, a point to ponder. Storing spambot IP addresses is justifiable, but are we sure we should store ALL our visitor IPs in unsecured form (plain text) in our database? What if that information, their information, is stolen? What if the blog has politically or morally sensitive information and the thief incriminates a site visitor because his IP appears in avhfdas_ipcache? Shouldn't we at least anonymize or hash visitor IPs?
Good job on this plugin, Peter. My comment spam plugins and I are very pleased with your work.
Recent Posts