More efficient version of MySql query

zooks

I'm using the query below multiple times on one page. As table1 grows, the queries slow down significantly.

Is there a better way to organize this? Am I gumming up the query somehow? Is there a faster way to query this data all together?

Thanks for any advice or guidance...

$sql = $pdo->query('
      SELECT AVG(table1.VAR1), table1.ID, table2.VAR2, table2.VAR3, table2.VAR4
      FROM table1
      LEFT JOIN table2 ON table1.ID = table2.ID
      GROUP BY table2.VAR2
      ORDER BY AVG(table1.VAR1) 
      DESC 
      LIMIT 5');

foreach ($sql as $row) {
   //echo results  }

Im running PHP 5.2.4 and MySQL 5

sneakyimp

when you use sql functions like AVG and GROUP BY, then you are forcing the db engine to look at all the records in the table (or a lot anyway). Do you have indexes in your db on table1.ID and table2.ID? how about table2.VAR2?

zooks

@ sneakyimp

Thanks for the quick response.

So both tables have a primary key field, but I'm realizing that I don't have the ID field indexed in either.

I just found a useful article about mysql indexes and how they can really speed things up...so before I create my indexes and alter tables:

Is there a big performance difference between Normal Index vs. Unique Index? ( table1 is ~6500rows[static] while table2 ~100rows[and growing])

I can use the Unique Index for table1 but not for table2.

Thanks again for pointing this out

sneakyimp

I don't really know if unique indexes are faster or slower. I do know that they require each record to have a different value for that field. If you foresee ever having two different records with the same value, you cannot use a unique index.

zooks

Since in my case, field ID will always be unique in table1, I created a unique index using the following MySQL statement:

CREATE UNIQUE INDEX index_id ON table1 (id)

It worked like a charm, the queries are running nicely! 🙂

Thanks for your help!