Querying posts table..
 

So my boards have ~1.3m posts now.. and when I try to do a query from my shell
SELECT * FROM vb3_post WHERE ipaddress LIKE 'blah%';

the result is:

1995 rows in set (4 min 8.30 sec)

The current load on the server is:
[nolimit@ded-lax-016 ~]$ uptime
06:27:52 up 19 days, 17:31, 3 users, load average: 11.14, 11.27, 8.91

Is this supposed to happen given the load? Or any idea of what could be wrong?

You have to add an index to your post table

ALTER TABLE post ADD INDEX (ipaddress);

I think...

-----

In future:

log-slow-queries
log_queries_not_using_indexes
long-query-time = 1

Add this to your my.cnf and fix all queries that show up in your log.

(Do an EXPLAIN on them and add appropriate indices.)

thanks will try that.

It is advised to close the forums while adding the index (best turn off your webserver) or you will get a ton of table locks.

orban,

Once you create the index, do you have to change the way you write the query to see the difference in performance?

Thanks,

Corey

No. To make sure the index gets used use "EXPLAIN SELECT..."