CODEWITHSUNDEEP
mysqldatabasedatabase-designindexing
stefmikhail
stefmikhail

Reputation: 7145

How to Determine Optimal MySQL Table Indexes, When Contents of WHERE Clause Vary?

I have the following 2 mysql_queries:

Query 1 (this query is repeated twice more for imgClass, and imgGender):

$imgFamily_query = "SELECT DISTINCT imgFamily FROM primary_images WHERE '$clause' ";

Query 2:

$query_pag_data = "SELECT imgId, imgURL, imgTitle, view, secondary FROM primary_images WHERE '$clause' ORDER BY imgDate DESC";

As you can see, the WHERE is controlled by a variable. This variable is calculated as follows:

$where_clauses = array();

if ($imgFamilyFalse && $imgClassFalse && $imgGenderFalse) {
    $where_clauses[] = "1=1"; // default do-nothing clause
}

if ($imgFamilyTrue) {
   $where_clauses[] = 'imgFamily=' . "'" . mysql_real_escape_string($_GET['imgFamily']) . "'";
}
if ($imgClassTrue) {
   $where_clauses[] = 'imgClass=' . "'" . mysql_real_escape_string($_GET['imgClass']) . "'";
}
if ($imgGenderTrue) {
   $where_clauses[] = 'imgGender=' . "'" . mysql_real_escape_string($_GET['imgGender']) . "'";
}


$clause = implode(' AND ', $where_clauses);

The WHERE clause is only dependant upon the following 3 columns:

  1. imgFamily
  2. imgClass
  3. imgGender

However, depending upon the situation, a combination of any 1, 2, or 3 of those columns are utilized.

My question is, how should I go about setting up the indexes for primary_images in this situation? It is a 'read-only' table, so I'm not concerned about having too many indexes. I would like the table to be as efficient in its querying as possible.

I was thinking about using a Multiple Column Index, but because the first column in the Multiple Column Index may not be present, the Index would not work.

Is it possible to set up several Multiple Column Indexes? Or would it be better in this case to just place an index on each of the 3 columns in question?

Upvotes: 0

Views: 171

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

I'm guessing imgGender will contain only 2 or 3 values - M, F and possible unknown? In that case, it makes a poor candidate for an index.

So, I think you can get away with 2 indices. Index one should use only imgClass, and will be hit when the imgFamily column isn't part of the where clause. Index two should be a compound index, using imgFamily and imgClass; this should be used even if imgClass isn't part of the where clause.

Upvotes: 3

Imdad
Imdad

Reputation: 6042

As per your situation its better to keep 3 separate indexes.

Upvotes: 1

Related Questions