I have a query that looks like this:

PHP Code:

$query = "SELECT page.*, COUNT(*) AS count FROM page JOIN hits USING(id_hit) WHERE hits.site_id=$id AND hits.utime BETWEEN $lower_bound AND $upper_bound AND (hits.status=1 OR hits.status=2) GROUP BY page.domain" 


At the moment, there is only one index on each table which are just the primary keys. I'm trying to make the query faster, so I add an index:

ALTER  TABLE hits ADD  INDEX sid_time_status(site_id, utime, status)

After adding the index, my query now takes 50% longer. !?! What am I missing?

don't use SELECT * in the query, use a fieldlist instead.

SELECT * makes the query optimiser make TWO passes through the table, one to get the column names, the second to get the data.

Originally Posted by chrishirst

don't use SELECT * in the query, use a fieldlist instead.

SELECT * makes the query optimiser make TWO passes through the table, one to get the column names, the second to get the data.

Hmm, I tried that. It helps slightly, but not by a lot:

SELECT page.domain, COUNT(*) AS count FROM page JOIN hits USING(id_hit) WHERE hits.site_id=9 AND hits.utime BETWEEN 1183269600 AND 1198479599 AND (hits.status=1 OR hits.status=2) GROUP BY page.domain

I tried some other things, like adding id_hit to my new index. This makes it take nearly the same amount of time as without an index, but not slower:

ALTER TABLE hits ADD INDEX sid_time_status(id_hit, site_id, utime, status)

I notice that if I remove both the COUNT() AS count and GROUP BY page.domain, then the query is extremely quick. I've tried an index on page.domain, and no benefit seems to come of it. I'm thinking it might be faster just to get the rows without the count and just code a count in PHP.

You still have COUNT(*) in there which counts all the rows of all the columns. Just pick one column if you only need a row count.

Or you could use mysql_num_rows

Have you tried running EXPLAIN with the query?

Originally Posted by chrishirst

You still have COUNT(*) in there which counts all the rows of all the columns. Just pick one column if you only need a row count.

Or you could use mysql_num_rows

Have you tried running EXPLAIN with the query?

I wasn't sure if COUNT(column) was any faster because I stumbled across this site. It says using COUNT(*) is quicker for myisam because the number of rows are already cached, but I don't know if that still holds true with a GROUP BY. Anyway, I did try several different columns, and the query time didn't change significantly.

I think mysql_num_rows won't work with what I'm doing. I'm trying to get a count of each domain (hence the GROUP BY domain)

I've tried running EXPLAIN... I'm not familiar with it though, so I'm in the process of learning how to interpret the results.