|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Codewalkers Forums Sponsor:
|
 AT&T devCentral & BlackBerry(r) Webcast Series: BlackBerry and GPS -Build Location Awareness into your BlackBerry Applications, July 10th-1:00PM EST. Register Today!
|
|
#1
November 6th, 2003, 08:46 PM
|
||||
|
||||
|
Improving a query
I've got two queries that are really slow.
My tables look like this: Table A: id (primary key) Has approx. 140,000 records Table B: id (primary key) a_id (there's an index on this) created_datetime Has approx. 1,000,000 records For the first query, I want to find the number of A's that have a certain number of B's: Code:
SELECT a_id, COUNT(*) AS cnt FROM b WHERE a_id > 0 GROUP BY a_id HAVING cnt >= 10 AND cnt < 20 The problem is, I really only want the total number of A's, not each one, so after I do the query, I simply look at mysql_num_rows(). The second query is similar, but instead of the number of B's, I want to look at the time difference between the last and the first. Code:
SELECT a_id, MAX(UNIX_TIMESTAMP(created_datetime))-MIN(UNIX_TIMESTAMP(created_datetime)) AS t FROM b WHERE a_id > 0 GROUP BY a_id HAVING t >= 600 AND cnt < 1200 Again, all I want is the number of rows from this and it seems like I'm wasting a lot of processing by doing it this way. Any ideas?
|
|
#3
November 6th, 2003, 08:55 PM
|
|||
|
|||
|
RE: Improving a query
Also, on average, how many times do yu run those queries before you modify those tables?
Maybe you can cache the results in a heap table, helps sometimes.
|
|
#4
November 6th, 2003, 11:04 PM
|
||||
|
||||
|
RE: Improving a query
By slow, I think they were taking about 10 seconds each. I decided cacheing was the way to go. Thanks for the suggestion though.
|
|
| Viewing: Codewalkers Forums > Other Technologies > Database Help > Improving a query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
