Optimize mysql queries

[jrknierim]

I work at a shared hosting company, and notice often some (often times large, the example below isn't) queries that are not indexed. I thought I would include some examples and indexes that I've added which have reduced mysql processing. Thank you.

Urls table, there is no index on url.

# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 26419 SELECT id from wp_firestats_urls WHERE url = '<hidden>';

alter table wp_firestats_urls add index url (url (75));

Useragents table, there is no index on useragent.

# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 4307 SELECT id,match_bots from wp_firestats_useragents WHERE useragent = '<hidden>';

alter table wp_firestats_useragents add index useragent (useragent (35));

[omry]

Thanks. I did some very serious optimizations to the hits processing, but I somehow missed this one.

adding an index on the url/useragent is probably not the right thing to do, but instead use the existing index on the md5 column.

I will take a closer look before the 1.6 release.

[omry]

okay, improved the processing of hits in immediate mode (the problem you reported is relevant to it) by using the md5 index and doing a few other things. (can now handle around 500 hits/sec on my development machine).

in addition, I profiled the processing of hits in the batched mode (which is recommended for heavily loaded servers) and improved it's performance by 40% (it can now commit 1600hits/sec on my development machine).

all in all, I am very satisfied with those results.

thanks again for letting me know.