[Urgent !!] MySQL performance problem with Fuzzy SEO

I am now using Vbulletin 4.1.3 and Fuzzy SEO 1.5.0b.
The SEO result is very good, however once the table keep growing it cause mysql to bad performance.
Here is the current number of record in SEO Fuz table

seoqueries_data ~1,775,553 InnoDB utf8_unicode_ci 85.7 MiB -
seoqueries_filtered_terms 0 MyISAM utf8_unicode_ci 1.0 KiB -
seoqueries_terms ~815,616 InnoDB utf8_unicode_ci 357.7 MiB -
seoqueries_terms_stats 139,778 MyISAM utf8_unicode_ci 11.8 MiB 374.4 KiB


99% of time I saw this log in slow query log of mysql (note: I have used mysqlsla to analyst the mysql-slow.log ).
Count         : 3.79k  (96.83%)
Time          : 42800 s total, 11.298838 s avg, 3 s to 109 s max  (98.41%)
  95% of Time : 29543 s total, 8.210951 s avg, 3 s to 33 s max
Lock Time (s) : 470 s total, 124.076 ms avg, 0 to 47 s max  (97.92%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 40 avg, 0 to 100 max  (97.62%)
Rows examined : 1.77M avg, 1.77M to 1.77M max  (97.98%)
Database      : vb_utf8
Users         :
        vb@localhost  : 100.00% (3788) of query, 99.92% (3909) of all users

Query abstract:
SELECT st.*, sd.founded FROM seoqueries_data sd LEFT JOIN seoqueries_terms st ON sd.stid = st.stid WHERE st.ban = N AND sd.page_type = 'S' AND sd.page_id = N ORDER BY sd.founded DESC LIMIT N;

Query sample:
SELECT^M
                                                        st.*, sd.founded^M
                                                FROM^M
                                                        seoqueries_data sd^M
                                                LEFT JOIN^M
                                                        seoqueries_terms st^M
                                                        ON^M
                                                        sd.stid = st.stid^M
                                                WHERE^M
                                                        st.ban = 0^M
                                                        AND^M
                                                        sd.page_type = 'showthread'^M
                                                        AND^M
                                                        sd.page_id = 5999^M
                                                ORDER BY^M
                                                        sd.founded DESC LIMIT 100;


Is there anyway to improve the speed of this query?
It everytime I am loading the page it is very slow, sometime take upto 10-20 seconds.
Sign In or Register to comment.