当WHERE子句内容变化时，如何确定最佳MySQL表索引？ [英] How to Determine Optimal MySQL Table Indexes, When Contents of WHERE Clause Vary?

问题描述

我有以下2 mysql_queries ：

I have the following 2 mysql_queries:

查询1（此查询对于 imgClass 和 imgGender ）重复两次：

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

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

查询2：

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

如您所见， WHERE 由变量控制。该变量计算如下：

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);

WHERE 取决于以下3列：

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

1. imgFamily


2. imgClass


3. imgGender

1. imgFamily
2. imgClass
3. imgGender

但是，根据情况，可以使用这些列中的任何1,2或3的组合。

我的问题是，在这种情况下，我应该如何设置 primary_images 的索引？它是一个'只读'表，所以我不担心索引太多。我希望表格尽可能高效地查询。

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.

可以设置几个多列索引吗？或者在这种情况下更好的是在每个3列中放置一个索引？

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?

推荐答案

我是猜测imgGender将只包含2或3个值 - M，F和可能的未知数？在这种情况下，它使得索引的候选人不佳。

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.

所以，我想你可以摆脱2个索引。索引应该只使用imgClass，当imgFamily列不是where子句的一部分时，它将被击中。索引二应该是一个复合索引，使用imgFamily和imgClass;即使imgClass不是where子句的一部分，也应该使用这个。

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.

这篇关于当WHERE子句内容变化时，如何确定最佳MySQL表索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！