www.webdeveloper.com
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Oct 2006
    Posts
    15

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

    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

    Code:
    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)

  2. #2
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

  3. #3
    Join Date
    Oct 2006
    Posts
    15
    Quote Originally Posted by bubbisthedog
    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?

  4. #4
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    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/...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.
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

  5. #5
    Join Date
    Jul 2004
    Posts
    300
    Quote Originally Posted by Psytherium
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles