Re: MySQL SELECT performance suggestions

You don't need to select all the data to get the number of rows. Try this: select count(*) as total from products where $product_query; Then use total instead of num_rows. Be sure the elements in your where clause are properly indexed. webbie@deanox.com wrote: > > Could someone suggest some options here for performance improvement? > > I have a table with about 300,000 entries/records by 7 columns/fields. All of > the information varies from entry, but only one field can be declared as UNIQUE. > > I have PHP perform SELECT queries on the table, and I need to display the > results in a format of: "viewing $top to ($top+$depth) of $numrows found" > > I haven't found a way to determine $numrows without performing a second SELECT > in this format: > > $numrows = mysql_num_rows (db_query("SELECT * FROM products WHERE > $product_query")); > if ( $numrows < $depth ) { $depth = $numrows; } > $qid = db_query("SELECT * FROM products WHERE $product_query LIMIT $top, > $depth"); > > So, this takes about twice as long as it would without figuring the $numrows. > Does anyone have any suggestions as far as what can be done to speed this up? > Assume that the table in question is optimized with indexing and UNIQUE values. > > Thanks. > > Lee Howard > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net