PDA

View Full Version : Querying posts table..


randominity
07 Oct 2006, 14:30
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
07 Oct 2006, 15:31
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
07 Oct 2006, 15:37
thanks will try that.

orban
07 Oct 2006, 15:38
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
13 Oct 2006, 01:22
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
13 Oct 2006, 01:23
No. To make sure the index gets used use "EXPLAIN SELECT..."