mysql query running slow

Question

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 ;

post the output of the EXPLAIN of the query, just add EXPLAIN to the beginning — Sandeepan Nath
– Sandeepan Nath, Commented Dec 6, 2010 at 7:55
How much time is the query taking? There are very few records and no joins at all. It should get fixed with the proper indexes. — Sandeepan Nath
– Sandeepan Nath, Commented Dec 6, 2010 at 8:00

Sandeepan Nath · Accepted Answer · 2010-12-06 09:52:48Z

2

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.

edited Dec 6, 2010 at 9:52

answered Dec 6, 2010 at 7:53

Sandeepan Nath

10.3k18 gold badges92 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pentium10 · Accepted Answer · 2010-12-06 07:29:06Z

1

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.

answered Dec 6, 2010 at 7:29

Pentium10

209k124 gold badges435 silver badges513 bronze badges

2 Comments

acctman Over a year ago

i've added it to my original post

Pentium10 Over a year ago

Ok cool, now add the indexes I mentioned, and see how performs

cherouvim · Accepted Answer · 2010-12-06 08:33:50Z

1

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.

edited Dec 6, 2010 at 8:33

answered Dec 6, 2010 at 7:48

cherouvim

32k15 gold badges107 silver badges158 bronze badges

Comments

cristian · Accepted Answer · 2010-12-06 09:57:29Z

0

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

answered Dec 6, 2010 at 9:57

cristian

8,7443 gold badges40 silver badges44 bronze badges

Collectives™ on Stack Overflow

mysql query running slow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related