CODEWITHSUNDEEP
mysqldbforge
user756659
user756659

Reputation: 3512

mysql optimization (explain) with dbforge

I am in the process of reviewing my queries with dbForge to ensure they are optimal or optimal as can be. I have the following which I use for practically everything and the results are scaring me a bit as long as I am reading them correctly.

The basic gist of the db setup is accounts, computers, users, (tables that link to users). Any number of computers are linked to an account, with any numbers of users linked to each computer, with all other tables being linked to a user.

I use the following to only return results from 'active' computers. By supplying :account_id and :account_licenses I can do this. For example, 1 and 3 would only return results from the first 3 computers (sorted by its id) for account 1. Hopefully that makes sense.

SELECT *
FROM
( SELECT account_id, computer_id
    FROM computers
    WHERE account_id = :account_id
    ORDER BY computer_id ASC LIMIT 0, :account_licenses
) as c
INNER JOIN users
    on users.computer_id = c.computer_id

...further joins which act on user_id

the results from dbforge which are scaring me :

table        id     select type    type    possible keys    key    key len    ref    rows    extra
<derived2>  1   PRIMARY ALL                 5   
computers   2   DERIVED ALL unique_filter   unique_filter   4       14  Using filesort
users   1   PRIMARY ref unique_filter   unique_filter   4   c.computer_id   1

What scares me is the computers table appears to be scanning every row in its table (14). 14 is the total rows in this particular table at the time (I am just testing so not much data), but when this grows I certainly do not want to be scanning the entire table every time.

Is this what is actually happening or am I reading this wrong?

EDIT:

Sorry, should have noted the indexes.... accounts.account_id, computers.account_id, computers.computer_id, users.computer_id, users.user_id

Upvotes: 0

Views: 324

Answers (1)

Slade
Slade

Reputation: 2453

This might sound like I'm pointing out the obvious but you've not mentioned indexes in your question text.

The query might scan your entire computers table if you don't have an index on account_id. Some SQL processes may still scan the whole table if you only have a few row in it and 14 is what I would call 'not many rows'.

I think the best thing you can do is check the indexes and add lots of records (say 50-100) into the table and then test it for efficiency.

Upvotes: 1

Related Questions