0

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?

stefmikhail
  • 6,877
  • 13
  • 47
  • 61
  • 2
    Don't forget to also put an index on the `1=1` column. – Johan Sep 23 '11 at 07:43
  • @Johan - I should have asked you this when you wrote it, but how would I go about putting an index on the `1=1` column? I've been thinking about it and can't figure it out. – stefmikhail Oct 16 '11 at 03:48
  • I was joking there, it's clearly impossible :-) – Johan Oct 16 '11 at 11:09
  • @Johan - Well at least I don't feel like an idiot anymore, except that I didn't get the joke of course. Any suggestion on how I can get that situation to use an index? It's ordered by date decending, but an index on the date column isn't being used, and neither is a compound primary index that includes the date column. Maybe it doesn't matter? The table will never be more than 1000 rows. – stefmikhail Oct 16 '11 at 12:21
  • 1
    And index on gender is useless because of its low cardinality. So you should have 3 indexes: A: on imgdate only. B: on imgClass, imgDate C: on IngFamily, Imgdate. If MySQL refuses to use the index, that will be because it is faster to skip the index. This is usually because of low cardinality of the index. – Johan Oct 16 '11 at 12:41
  • @Johan - That's actually exactly what I ended up with on my own using explain. It's good to hear I'm learning and am on the right track. I appreciate your help, and your humour ;) – stefmikhail Oct 16 '11 at 14:11
  • @Johan - Last thing, the imgdate index should be unique, while the others should be non-unique indexes correct? There is never two entries with the same date, but there are many entries with the same class and/or family. – stefmikhail Oct 16 '11 at 14:14
  • 1
    A unique index will prevent you from inserting 2 items with the same data in the field(s) covered by that index. If that's what you want you should do it. A unique index does not make your selects any faster. It just slows down your inserts (and updates) because it has to check for the uniqueness requirement. – Johan Oct 16 '11 at 15:04
  • @Johan - Thanks again for the advice. It's very informative and useful. – stefmikhail Oct 16 '11 at 18:16
  • @Johan - I might as well ask you one more thing. What should I have as my primary Index? Right now it is `ImgId` which is unique and auto-incriment. It links to the foreign key of another table. Should I have it as just `imgId`, or as `imgId` and `imgDate`? – stefmikhail Oct 16 '11 at 18:26

2 Answers2

3

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.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • You are correct; `imgGender` contains only `male` and `female`, and defaults to `male`. Why is it that `imgGender` is a poor candidate? Your idea is an interesting one. Would it also work to have two compound indexes? `imgFamily` & `imgClass`, and `imgClass` & `imgFamily`? – stefmikhail Sep 22 '11 at 20:42
  • +1, @stefmikhail, If 30%+ of your rows have the same value, MySQL (any SQL actually) will refuse to use an index. This is called low cardinality. Indexes are a form of indirect lookup and will slow things down if a very large % of rows need to be looked up. – Johan Sep 23 '11 at 07:35
  • If it's a read-only table a third index, on only `imgFamily` might also be a good idea. It's a shorter index and will be (slightly) faster to use when only referencing that column. – Johan Sep 23 '11 at 07:40
1

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

Imdad
  • 5,942
  • 4
  • 33
  • 53
  • Multi-column indexes could improve the performance when more than one column is specified in the where clause. – a'r Sep 22 '11 at 20:34
  • @a'r - I agree, however I would have to create 3 in this case (I believe). Is that ok? To have 3 different Multiple Column Indexes? – stefmikhail Sep 22 '11 at 20:37
  • 1
    @stefmikhail, well given your statement that many indexes is not a problem, then you could add every permutation of indexes, which gives you at least 15 indexes. – a'r Sep 22 '11 at 20:40
  • @a'r - Would 15 really be necessary? The way I see it, 3 would be *more* than enough: 1) `imgFamily, imgClass, imgGender` 2) `imgClass, imgGender, imgFamily` 3) `imgGender, imgFamily, imgClass` – stefmikhail Sep 22 '11 at 20:53
  • @Imdad - Care to elaborate a bit? Comment on the comments we have left here under your question? – stefmikhail Sep 22 '11 at 20:55
  • @stefmikhail, well if you only filter on one of the columns, then a single column index will be more efficient than a two or three column index. – a'r Sep 22 '11 at 20:55
  • @a'r - What do you mean by "...if you only filter on one of the columns..."? – stefmikhail Sep 22 '11 at 20:57
  • 1
    See if you are using 3 different indexes in table whatever comes in where condition will be scanned in index. If something is not included in where clause then its index is not scanned. But if you put multi-column index and one of its candidate is not included in where clause then it will be like scanning 2 indexes when only one is required. I have used 3 indexes in my tables. It is safe. There is no problem in using 3 indexes. – Imdad Sep 22 '11 at 20:59
  • @Imdad - Great explanation. So essentially, if I use my 3 Multiple-Column Index idea, I would be wasting indexes, and making the database do more work than is required? Should Multiple-Column Indexes only be used when the two columns are *always* used together in a query? Like if both are in a `WHERE` clause? Or if one column is used in the `WHERE`, and another is used to `ORDER`? – stefmikhail Sep 22 '11 at 21:16
  • Use multiple column index if both are used in same place. – Imdad Sep 22 '11 at 21:23