Is this typical MySQL performance?

15 seconds to retrieve 2.7M/4.0M records on unloaded server

[MichaelBluejay]

I'm hoping someone can tell me whether the performance I'm experiencing is normal, and how I might improve performance.

15 seconds (2.7M records found)
count(*) FROM impressions WHERE size='125x250' AND rawtime >= '1130530338' AND rawtime <= '1137306089'

7 seconds (1.4M records found)
count(*) FROM impressions WHERE size='468x60' AND rawtime >= '1130530338' AND rawtime <= '1137306089'

The table has 4.1 million records. The fields used in the query are:

size: varchar(7) Indexed
rawtime: int(11) Indexed

In case it matters, the other two fields are:

creative: tinyint(4) Indexed
pageid: smallint(6) Indexed

I ran both ANALYZE and OPTIMIZE before running the query.

The hardware is Pentium IV with 512Mb of RAM.

The server isn't under heavy load. The TOP command showed the server load average right before running the query as 0.01, 0.08, 0.08.

Memory shows as 481Mb used, 32Mb free, and swap as 65Mb used, 2032Mb free. Maybe free memory is a problem? The TOP command shows that most of the memory is taken up by several mySQL processes, some listed for hours, with the command "mysqld--euler". Maybe earlier queries left the faucet running or something? I don't know whether this is an issue.

[txbakers]

That seems like a long time.

512 MB is rather small - grow it to at least 1GB of Ram.

Also Scalar functions like count, sum, max, etc. do tend to take a long time as well since it has to double double processing - reading rows based on your WHERE clause and keeping a running total.

If you can avoid the Count, and do the counting yourself, you might get faster results.

Also, in your where you can use a BETWEEN command instead of two commands, which should speed it up a little.

And do look to see if you are leaving connections open or objects not destroyed.

[MichaelBluejay]

Thanks for the help.

One thing I forgot to mention was that the 15 and 7 seconds figures are actuall for *seven* queries respectively, one for each of seven "size"s. So considering that, maybe this is typical.

I tried the queries directly in phpMyAdmin and it wasn't any faster than with my Perl script.

I did try to just select all the data, which is very fast, but doing the "count"ing with Perl took longer than doing it with MySQL.

I wasn't aware of the BETWEEN syntax, which is definitely handy. It didn't speed up my results, though.

I went through my various scripts and added "$DB->disconnect;" to the end of any script where that was missing. Is that all I need to do to make sure that when it's finished it no longer shows up in the TOP command? And how important is it to close out cursors with "$cursor->finish;"?

[FridayNight]

i hope you are not doing same mistake i did years ago. i always created one index with one field. in your case, you are using two fields after WHERE part. you should create one index with both fields in it, i hope you understand:

count(*) FROM impressions WHERE size='125x250' AND rawtime >= '1130530338' AND rawtime <= '1137306089'

create an index with:

- size
- rawtime

it is important that 1st field in your index is "size" and "rawtime" second! they must match positions in WHERE clause. try this and let me know.

in case you dont know, mysql will be able to use "size" index (without rawtime) too. i mentioned this because i also used to create another index "size" (if i had WHERE (size=...)) but it wasnt needed.

yea, it hurts if your indexes arent setup as they should be :) feel free to PM me for more info.

[MichaelBluejay]

Thanks, FridayNight. I didn't realize that it was possible to create a single index based on multiple fields. I did that and cut my execution time down from about 26 seconds to 10. That's good enough for now.

I'm going to reset my server in the middle of the night when traffic is slow and see if those phantom MySQL processes that show up in TOP are gone for good now that I added "$DB->disconnect;" to all files where it was missing.