CODEWITHSUNDEEP
phpmysqlperformance
acctman
acctman

Reputation: 4349

mysql query running slow

Is there a way to speed up this query. Its loading slow and its about 279 records. I've indexed the Language & id field in the data. Is there anything else I can do?

echo "<select name=language style='width: 136px;'>";
echo "<option value=$language selected=selected>-- Select --</option>";
$group1 = '<optgroup label=Common>';
$group2 = '<optgroup label=Alphabetically>';
$group = mysql_query("SELECT language, grouping, p_order FROM lang_list 
            WHERE grouping IN ('1','2') ORDER BY p_order");
while($row = mysql_fetch_array($group))
{
  if ($row['grouping'] == '1')
  {
       $group1 .= '<option value="'.$row['language'].'">'.
            $row['language'].'</option>';
  }
  else
  {
       $group2 .= '<option value="'.$row['language'].'">'.
            $row['language'].'</option>';
  }
    $group1 .= '</otpgroup>';
    $group2 .= '</otpgroup>';

    echo $group1;
    echo $group2;
    echo "</select>";
}

Table 

lang_list (
  id int(8) NOT NULL auto_increment,
  language varchar(75) NOT NULL,
  grouping varchar(15) NOT NULL,
  p_order varchar(15) NOT NULL,
  PRIMARY KEY  (id, language)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=280 ;

Upvotes: 1

Views: 388

Answers (4)

cristian
cristian

Reputation: 8744

Try a compossed index on groupping and p_order. Read more about group by optimization

Upvotes: 0

Sandeepan Nath
Sandeepan Nath

Reputation: 10284

You need to index what you are matching against.

Just like in a book, page number of all chapters are given in the index because readers search by chapter, similarly for Mysql to process your queries faster, you need to index the fields which will be matched in the where condition. Index the fields which others have suggested here.

Upvotes: 2

cherouvim
cherouvim

Reputation: 31921

create index I_grouping_order on lang_list (grouping, p_order);

If you want to know the details have a look at http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Note that adding two separate indexes (one for grouping and one for p_order) will not completelly do the trick because you are using both fields in your query at the same time.

Upvotes: 1

Pentium10
Pentium10

Reputation: 207830

You need to index also grouping and p_order. You can drop index on language if not used by any where condition.

Please post the table schema for further investigation, and some sample data.

Upvotes: 1

Related Questions