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?

[orban]

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.)

[randominity]

thanks will try that.

[orban]

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.

[rootnik]

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

[orban]

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