Announcement

Announcement Module
Collapse
No announcement yet.

Bad Performance or Bad Query ?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Bad Performance or Bad Query ?

    I have three tables like this:

    receipts
    +------------+-------+------+------------+-----------+| customerId | recID | plan | recDate | processed |+------------+-------+------+------------+-----------+| 9742 | 900 | 1 | 1178726955 | 0 || 2188 | 899 | 1 | 1178670358 | 0 || 2657 | 898 | 1 | 1178639090 | 0 || 9699 | 897 | 1 | 1178627303 | 0 || 1247 | 896 | 1 | 1178570875 | 0 |+------------+-------+------+------------+-----------+

    customer
    +-------------------------------+----------+-----------+| Email | LastName | FirstName |+-------------------------------+----------+-----------+| user1@domain.com | lname | fname || user2@domain.com | lname | fname || user3@domain.com | lname | fname || user4@domain.com | lname | fname || user5@domain.com | lname | fname |+-------------------------------+----------+-----------+

    user
    +----------+--------------+-------------+| Approved | UserUpgraded | UpgradeDate |+----------+--------------+-------------+| 1 | 1 | 1176134955 || 1 | 1 | 1176078358 || 1 | 1 | 1176047090 || 1 | 1 | 1176035303 || 1 | 1 | 1175978875 |+----------+--------------+-------------+


    each table is actually much bigger than that, over 10,000 entries ... now i need to get all three table's info at once ... since there is no index associated with any of those (poorly designed ... not by me) i am forced to use the WHERE clause.

    now this is where i get messed up ... this query:


    SELECT DISTINCT receipts.customerId, receipts.recID, receipts.plan, receipts.recDate, receipts.processed, customer.Email, customer.LastName, customer.FirstNameFROM `receipts`, `customer`WHERE receipts.customerId = customer.CustomerIdGROUP BY receipts.customerIdORDER BY receipts.recID DESCLIMIT 5;

    produces the following result:

    +------------+-------+------+------------+-----------+-------------------------------+----------+-----------+| customerId | recID | plan | recDate | processed | Email | LastName | FirstName |+------------+-------+------+------------+-----------+-------------------------------+----------+-----------+| 9742 | 900 | 1 | 1178726955 | 0 | user1@domain.com | lname | fname || 2188 | 899 | 1 | 1178670358 | 0 | user2@domain.com | lname | fname || 2657 | 898 | 1 | 1178639090 | 0 | user3@domain.com | lname | fname || 9699 | 897 | 1 | 1178627303 | 0 | user4@domain.com | lname | fname || 1247 | 896 | 1 | 1178570875 | 0 | user5@domain.com | lname | fname |+------------+-------+------+------------+-----------+-------------------------------+----------+-----------+5 rows in set (0.01 sec)



    which is fine ... but as soon as i add my third and last table into play .. the query takes about 2 mins to process and uses 50+% CPU ... this is the query i try to run:


    SELECT DISTINCT receipts.customerId, receipts.recID, receipts.plan, receipts.recDate, receipts.processed, customer.Email, customer.LastName, customer.FirstName, user.Approved, user.UserUpgraded, user.UpgradeDate FROM `receipts`, `customer`, `user` WHERE receipts.customerId=customer.CustomerId AND customer.Email=user.UserEmail GROUP BY receipts.customerId ORDER BY receipts.recID DESC;LIMIT 5;


    am i doing something wrong ? any help would be appreciated

    p.s. the reason im doing distinct and group by customer id from the receipts table is because there are multiple receipts for each customer ... and i want to get the latest one!

    p.s.s. the user table also has an email column, thats how the user and customer tables are linked ...

  • #2
    oh and i forgot ... system info:

    Processor: 4 x Xeon 3 Ghz
    OS: RedHat Linux
    Memory : 2 GB
    Mysql: 4.1.22-standard

    Comment


    • #3
      Show me result for EXLAIN SELECT for both queries.

      Example

      EXPLAIN SELECT
      DISTINCT receipts.customerId,
      receipts.recID,
      receipts.plan,
      receipts.recDate,
      receipts.processed,
      customer.Email,
      customer.LastName,
      customer.FirstName,
      user.Approved,
      user.UserUpgraded,
      user.UpgradeDate
      FROM
      `receipts`,
      `customer`,
      `user`
      WHERE
      receipts.customerId=customer.CustomerId AND
      customer.Email=user.UserEmail
      GROUP BY
      receipts.customerId
      ORDER BY
      receipts.recID DESC;
      LIMIT
      5;

      Comment

      Working...
      X