will a mysql query run slower if one of the tables involved has no index defined?

Question

There's this already populated database which came from another dev.

I'm not sure what went on in that dev's mind when he created the tables, but on one of our scripts there is this query involving 4 tables and it runs super slow.

    SELECT 
       a.col_1, a.col_2, a.col_3, a.col_4, a.col_5, a.col_6, a.col_7
    FROM
       a, b, c, d 
    WHERE
       a.id = b.id
   AND b.c_id = c.id
   AND c.id = d.c_id
   AND a.col_8 = '$col_8'
   AND d.g_id = '$g_id'
   AND c.private = '1'

NOTE: $col_8 and $g_id are variables from a form

It's only my theory that it's due to tables b and c not having an index, although I'm guessing that the dev didn't think that it was necessary since those tables only tell relations between a and d, where b tells that the data in a belongs to a certain user, and c tells that the user belongs to a group in d.

As you can see, there's not even a join or other extensive query functions used but this query which returns only around 100 rows takes 2 minutes to execute.

Anyway, my question is simply this post's title. Will a mysql query run slower if one of the tables involved has no index defined?

Answer 1

The answer is "it depends on details, but more likely than not it will be slower".

What the index allows you to do is to read only X% of the table's data from the disk instead of 100% (aka table scan).

Also, you need to run EXPLAIN anytime you want to answer "why is my query too slow" to get necessary details for a real explanation

Answer 2

No way to tell for sure without knowing the data distribution in thew table, and the query itself.. But You can pretty much guarantee that it won't br faster...

Answer 3

Generally, yes... but you have to define your indexes to the specific queries that you're running.

It looks like your tables should all have indexes on your id fields, c should have an index on private, and a should have indexes on the possible column values of a.col_8. However, the only way to really tell is to run your query with EXPLAIN or DESCRIBE to see how the engine is executing it...

Answer 4

YES! It will be MUCH slower.

You need to put indices on all the "IDs".

And probably should rewrite the query to use joins (will help the optimizer)...

Just after putting the indices there you can see hundred times performance increase.

The other thing that such a query says is that the previous developer was "A complete idiot (tm)"

M.