CODEWITHSUNDEEP
mysqljoinsql-order-byinner-join
cickit
cickit

Reputation: 31

MySQL query performance problem - INNER JOIN, ORDER BY, DESC

I have got this query:

                SELECT 
                t.type_id, t.product_id, u.account_id, t.name, u.username

                FROM
                types AS t

                INNER JOIN
                ( SELECT user_id, username, account_id 
                  FROM users WHERE account_id=$account_id ) AS u

                ON 

                t.user_id = u.user_id 

                ORDER BY 
                t.type_id DESC

1st question:

It takes around 30seconds to do this at the moment with only 18k records in types table.

The only indexes at the moment are only a primary indexes with just id.

Would the long time be caused by a lack of more indexes? Or would it be more to do with the structure of this query?

2nd question:

How can I add the LIMIT so I only get 100 records with the highest type_id?

Upvotes: 0

Views: 1950

Answers (2)

GolezTrol
GolezTrol

Reputation: 116110

Without changing the results, I think it is a 100 times faster if you don't make a sub-select of your users table. It is not needed at all in this case.

You can just add LIMIT 100 to get only the first 100 results (or less if there aren't a 100).

SELECT SQL_CALC_FOUND_ROWS /* Calculate the total number of rows, without the LIMIT */
  t.type_id, t.product_id, u.account_id, t.name, u.username
FROM
  types t
  INNER JOIN users u ON u.user_id = t.user_id
WHERE
  u.account_id = $account_id
ORDER BY
  t.type_id DESC
LIMIT 1

Then, execute a second query to get the total number of rows that is calculated.

SELECT FOUND_ROWS()

Upvotes: 1

Tim Gautier
Tim Gautier

Reputation: 30112

That sub select on MySQL is going to slow down your query. I'm assuming that this

SELECT user_id, username, account_id 
FROM users WHERE account_id=$account_id

doesn't return many rows at all. If that's the case then the sub select alone won't explain the delay you're seeing.

Try throwing an index on user_id in your types table. Without it, you're doing a full table scan of 18k records for each record returned by that sub select.

Inner join the users table and add that index and I bet you see a huge increase in speed.

Upvotes: 0

Related Questions