List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:August 22 2006 3:30pm
Subject:What to index?
View as plain text  
Hi,

I got 4 relatively big (for me at least) queries.  At the moment, the data 
in the tables are merely test data, but once the system goes into 
production, I'm expecting millions of records in most of the tables.  I'm 
trying very hard thus to optimise my queries and tables to ensure I get a 
reasonably good throughput once these tables start filling up, as altering 
the tables for indexes once populated is obviously something I want to try 
and avoid.

Having looked at one of the four queries (they're very similar), EXPLAIN 
jeilds the following:
mysql> EXPLAIN SELECT `UserChecks`.EntryID,
    ->        `UserChecks`.Attribute,
    ->        `UserChecks`.op,
    ->        `UserChecks`.Value
    ->   FROM `UserChecks`
    ->   LEFT JOIN `User` ON `UserChecks`.UserID=`User`.EntryID
    ->   LEFT JOIN `Group` ON `User`.GroupID=`Group`.EntryID
    ->   LEFT JOIN `GroupTimes` ON `GroupTimes`.GroupID=`Group`.EntryID
    ->   LEFT JOIN `Realm` ON `Group`.RealmID=`Realm`.EntryID
    ->   LEFT JOIN `Client` ON `Realm`.ClientID=`Client`.EntryID
    ->  WHERE `User`.isActive='y' AND
    ->        `Group`.isActive='y' AND
    ->        `Realm`.isActive='y' AND
    ->        `Client`.isActive='y' AND
    ->        `GroupTimes`.DOW=DAYOFWEEK(DATE_ADD('1970-01-01', INTERVAL 
UNIX_TIMESTAMP() SECOND)) AND
    ->        `GroupTimes`.StartTime < DATE_FORMAT(DATE_ADD('1970-01-01', 
INTERVAL UNIX_TIMESTAMP() SECOND), '%H:%i:%S') AND
    ->        `GroupTimes`.StopTime > DATE_FORMAT(DATE_ADD('1970-01-01', 
INTERVAL UNIX_TIMESTAMP() SECOND), '%H:%i:%S') AND
    ->        `Realm`.Realmname=SUBSTRING(TRIM(LOWER('user@stripped')), 
(LOCATE('@', TRIM(LOWER('user@stripped'))) +1)) AND
    ->        `User`.Username=LEFT(TRIM(LOWER('user@stripped')), 
(LOCATE('@', TRIM(LOWER('user@stripped'))) -1))
    ->  GROUP BY `UserChecks`.Attribute
    ->  ORDER BY `UserChecks`.Attribute;
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | 
ref  | rows | Extra                           |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | User       | system | PRIMARY       | NULL | NULL    | 
NULL |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | Group      | system | PRIMARY       | NULL | NULL    | 
NULL |    1 |                                 |
|  1 | SIMPLE      | Realm      | system | PRIMARY       | NULL | NULL    | 
NULL |    1 |                                 |
|  1 | SIMPLE      | Client     | system | PRIMARY       | NULL | NULL    | 
NULL |    1 |                                 |
|  1 | SIMPLE      | UserChecks | ALL    | NULL          | NULL | NULL    | 
NULL |    3 | Using where                     |
|  1 | SIMPLE      | GroupTimes | ALL    | NULL          | NULL | NULL    | 
NULL |    5 | Using where                     |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
6 rows in set (0.02 sec)

There are two items in my WHERE clause that does not use indexes.  One on 
the UserCheck table, and another on the GroupTimes table.

On the GroupTimes, I have DOW as a ENUM([1..7]) - I can more than likely add 
a index, but with a cardinality of 7, I doubt it will be worth it. 
StartTime and StopTime is a TIME field, I can add indexes on those.  I'm 
thus pretty sorted on the GroupTimes table (I think).

I have NO idea where in the WHERE clause I am referencing the UserChecks 
tables (except for the GROUP BY and ORDER BY).  I've added an index on the 
Attribute column (VARCHAR(32))  but the EXPLAIN still shows that it is not 
using the index....

Is the above optimised?  What can be done here to improve things....


Thanks allot,
Chris.

Thread
What to index?Chris Knipe22 Aug
  • Re: What to index?Chris23 Aug