[PHP-DB] Trying to reduce the number of queries

[Ryan Marks]

Hello all,

My script works fine as it is, but I am trying to reduce the number of
queries to the database.  I am running PHP 4.3.4 and MySQL 4.0.3.

Here is my table structure:

mysql desc application;
+-+--+--+-+-+---
-+
| Field   | Type | Null | Key | Default | Extra
|
+-+--+--+-+-+---
-+
| ID  | int(10)  |  | PRI | NULL|
auto_increment |
| media_ID| int(5)   |  | | 0   |
|
+-+--+--+-+-+---
-+

mysql desc mailbox;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra  |
+--+-+--+-+-++
| ID   | int(10) |  | PRI | NULL| auto_increment |
| app_ID   | int(5)  |  | | 0   ||
| company_ID   | int(5)  |  | | 0   ||
+--+-+--+-+-++

mysql desc media;
+---+-+--+-+-+--
--+
| Field | Type| Null | Key | Default | Extra
|
+---+-+--+-+-+--
--+
| ID| int(5) unsigned |  | PRI | NULL|
auto_increment |
| company_ID| int(5) unsigned |  | | 0   |
|
| media_type_ID | int(5) unsigned |  | | 0   |
|
| name  | varchar(100)|  | | 0   |
|
| state_ID  | tinyint(1) unsigned |  | | 0   |
|
| city  | varchar(50) |  | | 0   |
|
+---+-+--+-+-+--
--+

mysql desc media_type;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| ID| int(5) unsigned |  | PRI | NULL| auto_increment |
| type  | varchar(25) | YES  | | 0   ||
+---+-+--+-+-++

mysql desc state;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra  |
+--+-+--+-+-++
| ID   | int(5)  |  | PRI | NULL| auto_increment |
| full_name| varchar(25) | YES  | | NULL||
| abbreviated_name | char(2) | YES  | | NULL||
| region   | varchar(25) | YES  | | NULL||
+--+-+--+-+-++


Here is a part of my code.  I have a function query_database() that I use to
run mysql_connect() and mysql_query with all of the necessary error
handling.  I also use db_select_val() that returns the first field from the
first record of the results.

// Get a list of all of the types of media this company has listings
$query_type = select media_type.ID, type from media_type, media where
media_type_ID = media_type.ID and company_ID = \$company_ID\ and
deleted_date = \0\ group by media_type_ID;
$result_type = query_database($query_type);
while($row_type = mysql_fetch_assoc($result_type)){
$type_ID = $row_type[ID];
$type = $row_type[type];

// Get a count of the number of applications in this company's mailbox that
are the same type as// the $type_ID found above
$type_count = db_select_val(select count(*) from application, media,
mailbox where application.ID= mailbox.app_ID and media.company_ID =
mailbox.company_ID and application.media_ID = media.ID and
media.media_type_ID = \$type_ID\ and media.company_ID = \$company_ID\);
echo $type $type_count\n;

// Get a list of all of the media locatons this company has listings for
this media type
$query_state = select state.abbreviated_name, city, name, media.ID from
media, state where  state_ID = state.ID and company_ID = \$company_ID\ and
deleted_date = \0\ and media_type_ID =\$type_ID\ order by
state.full_name, city, name;
$result_state = query_database($query_state);
while($row_state = mysql_fetch_assoc($result_state)){
$state = $row_state[abbreviated_name];
$city = $row_state[city];
$name = $row_state[name];
$ID = $row_state[ID];

// Get a count of the number of applications in this company's mailbox
that are associated
// with this media location
$state_count = 

Re: [PHP-DB] Trying to reduce the number of queries

[Jochem Maas]

using grouping:

SELECT count(application.*), application.media_ID, mailbox.company_ID
FROM application, mailbox
WHERE mailbox.app_ID = application.ID
GROUPBY mailbox.company_ID, application.media_ID
SQL is unchecked;

try to look at the problem from the other direction
determine what information you want to extract the dive into
the mySQL manual to determine if there is any SQL syntax that
provides the data inherent in the database in the form you want it.
with regard to speed - oh boy it will!

they way I see it, in an 'information age' data is king - which means 
the database, being the closest to the king, should pre-process as much 
as possible - only do it is as little queries as poss. because each trip 
 to the data base is overhead:

n - num of rows
T - time for 1 good query
t - time for 1 bad query
O - connection overhead
single query| many queries
O+T | n*t + n*O
seeing as databases are optimized to perform the kind of action you 
require you can expect the saving to be considerable in technical terms 
- in real life you may see little change, none the less you know that 
you script is more effecient and can therefore handle a higher load.

notice the layout of the SQL;

$sql = '
SELECT count(application.*), application.media_ID, mailbox.company_ID
FROM application, mailbox
WHERE mailbox.app_ID = application.ID
GROUPBY mailbox.company_ID, application.media_ID';
little things like this help when you _have_ to go in and optimize the 
code a year after you last touched it.

if its your first app, you probably see no end to, after a few you begin 
to realise that they're all just one every evolving/mutating collection 
of code to which there is no end. the moral of the story trust your 
instincts. :-)

Ryan Marks wrote:

Hello all,

My script works fine as it is, but I am trying to reduce the number of
queries to the database.  I am running PHP 4.3.4 and MySQL 4.0.3.
Here is my table structure:
...

TIA,
Ryan Marks
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php