Anyway to speed up a count...it seems like this should run faster

[Psytherium]

I'm returning 20 cities that have the most reviews. There are about 41900 rows in the reviews table, and about 140000 total in the apartments table. I've pasted the select query that I use and the explain for it. It takes 1.19 seconds to perform this query. I would love for this to run sub 100/200ms, but is that an unreasonable goal? I have a quad core 3.00ghz xeon, 2GB ram, and a 10k SAS drive. Any help/criticism would be GREATLY appreciated.

Thanks,
Jon


mysql> SELECT COUNT(r.rid) as 'reviewCount', a.city, a.state
->FROM apartments a
->JOIN reviews r ON r.aid = a.aid
->WHERE a.state = 'TX'
->GROUP BY a.city, a.state
->ORDER BY reviewCount DESC
->LIMIT 20;

20 rows in set (1.19 sec)

mysql> EXPLAIN SELECT COUNT(r.rid) as 'reviewCount', a.city, a.state
-> FROM apartments a
-> JOIN reviews r ON r.aid = a.aid
-> WHERE a.state = 'TX'
-> GROUP BY a.city, a.state
-> ORDER BY reviewCount DESC
-> LIMIT 20;
+----+-------------+-------+------+------------------------+-------------------+
---------+-----------------------------+-------+--------------------------------
--------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra
|
+----+-------------+-------+------+------------------------+-------------------+
---------+-----------------------------+-------+--------------------------------
--------------+
| 1 | SIMPLE | a | ref | PRIMARY,state | state |
53 | const | 13209 | Using where; Using temporary; U
sing filesort |
| 1 | SIMPLE | r | ref | aid | aid |
9 | ardb.a.aid | 3 | Using where
|
+----+-------------+-------+------+------------------------+-------------------+
---------+-----------------------------+-------+--------------------------------
--------------+
2 rows in set (0.00 sec)

[bubbisthedog]

Check this out:

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

[Psytherium]

Check this out:

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

I read the article and added an index to my apartment table for state, city.

Without my ORDER BY clause, this speeds up the query to .08 seconds. This is awesome, but the second I add in my ORDER BY clause, it goes back to the crappy speed. Is there ANY solution?

[bubbisthedog]

Unfortunately, MySQL 5.0 (if that's what you're using) doesn't support Materialized Views (at least not to my knowledge), which allow you to literally store query results in a physical table. I use them at my job when I'm querying millions of records on a DB2 database, and they work magnificently. You may be able to find a vendor to allow you to add Materialized View functionality in MySQL.

This might help, although I have never used it myself:

http://dev.mysql.com/tech-resources/articles/mysql-events.html#15

Your other option would be to devise your own data mart/warehousing scheme. This can require a good bit of planning, though.

[mattyblah]

I read the article and added an index to my apartment table for state, city.

Without my ORDER BY clause, this speeds up the query to .08 seconds. This is awesome, but the second I add in my ORDER BY clause, it goes back to the crappy speed. Is there ANY solution?

Learn how your db handles queries, and take appropriate action. no silver bullet in programming, much less db programming.