Results 1 to 10 of 10
Thread: Speed up MySQL query
-
11-01-2007, 07:11 AM #1Web Hosting Master
- Join Date
- Aug 2000
- Posts
- 2,754
Speed up MySQL query
Hi Guy's,
I've got a table that has over 20,000 entries and it takes about 1 minute to return stats in a live environment.
This is my query:
PHP Code:$total=@mysql_query("SELECT
u.first_name, u.last_name, t.jobtype,
(SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = '1') AS '1',
(SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = '3') AS '3',
(SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = '4') AS '4',
(SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id) AS 'MegaTotals'
FROM
users u, taskinput t
WHERE
t.user_id = u.user_id AND u.tier = '$_SESSION[tier]'
GROUP BY
u.user_id, t.user_id
ORDER BY MegaTotals DESC");
-
11-01-2007, 07:21 AM #2Web Hosting Master
- Join Date
- Aug 2000
- Posts
- 2,754
Sorted, this can be closed I added a Join:
JOIN
taskinput t ON u.user_id = t.user_id
-
11-03-2007, 12:30 AM #3Web Hosting Master
- Join Date
- Aug 2002
- Location
- Canada
- Posts
- 667
-
11-08-2007, 03:39 PM #4Newbie
- Join Date
- Nov 2007
- Posts
- 7
Maybe you should consider adding memcached to your design? Memcached can really help speed up already optimized sql queries.
-
11-09-2007, 06:37 AM #5Junior Guru Wannabe
- Join Date
- Oct 2007
- Location
- Quezon City, Philippines
- Posts
- 65
20,000 records, 1 minute. have you considered other factors? Server load, hardware, etc. That's pretty slow. Imho.
at your service.
-
11-09-2007, 12:53 PM #6Newbie
- Join Date
- Nov 2007
- Location
- UK
- Posts
- 19
Have you made sure you have index'ed the key fields you're searching on?
-
11-10-2007, 08:05 AM #7New Member
- Join Date
- Nov 2007
- Posts
- 1
Try to ADD INDEX (tier) to table 'users'
-
11-18-2007, 06:24 PM #8Newbie
- Join Date
- Nov 2007
- Posts
- 10
you can put the database in memory
-
11-18-2007, 06:55 PM #9Junior Guru Wannabe
- Join Date
- Aug 2007
- Location
- 127.0.0.1
- Posts
- 92
-
11-22-2007, 04:48 PM #10Registered User
- Join Date
- Nov 2007
- Posts
- 40
hmm...Haven't got the time to really go over this but I think you are trying to get a userid from one table and then count the number of times each user is listed for each jobtype in a second table.
It would assume that there are other userids in the second table that are not in the first table....because if there were not, there is no reason to access the first since the second already has the user ids...
I am not really sure what you are trying to do here...
Can you be more specific or at least add the rest of the code...as in what you are trying to output for the user.?
I think the code may be for someone who is logged in to look at their jobtype thing...
heck, I is confused...if this is for one person, then yea, it is really convoluted..if it is for sorting all records, then it will take time using that method as there are 20,000 records that you are continually going through over and over for the same information...
And if you are just counting...there is no reason to do that..