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(&$sql_string);

function table_array ($sql_string)
{
$result = mysql_query($sql_string) or die("bang");
$num_rows = mysql_num_rows($result);
$num_fields = mysql_num_fields($result);
$j = 0;
$x = 1;
while ($row = mysql_fetch_array($result)) {
for($j = 0;$j < $num_fields;$j++) {
$name = mysql_field_name($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]

> $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]

Hi,

On Mon, 3 May 2004 20:48:32 -0800, "StinkFinger" <st****@pinky.com>
wrote:

$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 ?

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]

> 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?

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.