OT : Optimize MySQL queries

[StinkFinger]

All,
I am in the process of tweaking and tuning my PHP scripts, and am starting
to look in tweaking my indexes in my MySQL tables.

I have found many, many articles on the topic and have applied quite a few
tips from them and have increased the
overall speed of the site. Question I have is, there are quite a few queries
that I have that really don't have any
"index-able" fields, with the exception of a specialized "sort" field I
guess.

Anyways, should an index of some sort (i.e. single, multiple, etc.) be
created for a query like this:
(NOTE: I read the entire result into an array, and will process it on the
PHP page, basically creating
tables and such from the results).

$sql_string = "SELECT SQL_CACHE a,b,c,d,e,f,g,h FROM mytable ORDER BY
sortid";
$tb_array = table_array(&$s ql_string);

function table_array ($sql_string)
{
$result = mysql_query($sq l_string) or die("bang");
$num_rows = mysql_num_rows( $result);
$num_fields = mysql_num_field s($result);
$j = 0;
$x = 1;
while ($row = mysql_fetch_arr ay($result)) {
for($j = 0;$j < $num_fields;$j+ +) {
$name = mysql_field_nam e($result, $j);
$arr[$x][$name] = $row[$name];
}
$x++;
}
return array('arr' => $arr, 'rows' => $num_rows, 'fields' => $num_fields);
}

The only index I can think of to create might be on the "sortid" field.
I don't really have a need for a WHERE clause (I don't think), since I am
returning the entire result.

Any ideas ? Thanks.

[StinkFinger]

Re: Optimize MySQL queries

> $sql_string = "SELECT SQL_CACHE a,b,c,d,e,f,g,h FROM mytable ORDER BY
sortid";

just thinking here and still playing w/some ideas - would it be faster if i
was to do this:

SELECT SQL_CACHE a,b,c,d,e,f,g,h FROM mytable WHERE sortid IS NOT NULL ORDER
BY sortid;

and then create an index on sortid, i.e.

CREATE INDEX sortid ON mytable (sortid);

even though i know that sortid will never be null, having an index built on
it and adding a WHERE clause may help
it out. No ? Yes ?

go easy - i'm still learning...

[Jochen Daum]

Re: Optimize MySQL queries

Hi,

On Mon, 3 May 2004 20:48:32 -0800, "StinkFinge r" <stinky@pinky.c om>
wrote:
[color=blue][color=green]
>> $sql_string = "SELECT SQL_CACHE a,b,c,d,e,f,g,h FROM mytable ORDER BY[/color]
>sortid";
>
>just thinking here and still playing w/some ideas - would it be faster if i
>was to do this:
>
>SELECT SQL_CACHE a,b,c,d,e,f,g,h FROM mytable WHERE sortid IS NOT NULL ORDER
>BY sortid;
>
>and then create an index on sortid, i.e.
>
>CREATE INDEX sortid ON mytable (sortid);
>
>even though i know that sortid will never be null, having an index built on
>it and adding a WHERE clause may help
>it out. No ? Yes ?[/color]

No. It would help if around 80% of sortid IS NULL and the rest isn't.
You read basically the whole table and database query optimizers will
decide to do a table scan typically when they believe they will touch
20% of the data.

So, your index will not be used. If you have no where clause an index
is not useful. Is there a table which you could join to your table,
ie. are you combining results with a different table?

HTH,

Jochen

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/

[StinkFinger]

Re: Optimize MySQL queries

> No. It would help if around 80% of sortid IS NULL and the rest isn't.[color=blue]
> You read basically the whole table and database query optimizers will
> decide to do a table scan typically when they believe they will touch
> 20% of the data.
>
> So, your index will not be used. If you have no where clause an index
> is not useful. Is there a table which you could join to your table,
> ie. are you combining results with a different table?[/color]

No, I don't have any tables to join to. Now that I am learning more and more
about MySQL and PHP, I am starting to re-evaluate my original db design.
I may end up changing alot around in the near future.

Thanks for the help.