Results 1 to 10 of 10
  1. #1
    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"
    ); 
    Can anyone think of a way to speed this up?

  2. #2
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    667
    Joins are not usually the most effective means to do things.


    Can you show me a SHOW CREATE TABLE for each of the tables in your queries? Let's see if we can't squeeze some extra performance out of it.

    Cheers.
    Alex
    circlical - hosting software development
    forums * blog

  4. #4
    Maybe you should consider adding memcached to your design? Memcached can really help speed up already optimized sql queries.

  5. #5
    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.

  6. #6
    Have you made sure you have index'ed the key fields you're searching on?

  7. #7
    Try to ADD INDEX (tier) to table 'users'

  8. #8
    you can put the database in memory

  9. #9
    Join Date
    Aug 2007
    Location
    127.0.0.1
    Posts
    92
    Quote Originally Posted by ikkeook View Post
    you can put the database in memory
    I really hope you were not serious when you said that

  10. #10
    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..

Posting Permissions

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