Seeking php/mysql Optimization Tips

[robynne]

Good afternoon. I was hoping to get some query optimization tips.

I am writing a php script to retrieve data from a mySQL database. Table A, which contains most of the fields that I need, contains just under 11,000 rows. Table B, which contains 3 fields that I need, contains close to 45,000 rows. Table A's primary key (ID) is a foreign key in Table B (ID). My application requires only those two tables.

I am using a join that is similar to the following:

$query = "SELECT TableA.empl_fname, TableA.empl_lname, TableA.is_private, TableB.empl_uname, TableB.email_set 
AS empl_details from TableA 
JOIN TableB on TableA.ID = TableB.ID 
WHERE TableA.empl_lname like " . "'" . $empl_lname . "%' and TableA.is_private = 'N' and TableB.email_set = 'yes' " . 
" ORDER by TableA.empl_lname, TableA.empl_fname";

$empl_lname is retrieved from the end user. Although the join retrieves the data I need, it is extremely slow (over 30 seconds, in some cases). I also considered removing the join and using the following logic instead:
for each record in Table A returned via SELECT
use SELECT to obtain and display the record in Table B where TableA_ID = TableB_ID

That logic was slow, also - probably since each execution in the for loop includes a trip to the database.

Is it possible to rewrite the query to minimize the execution time? I think that each trip to the database to retrieve data in Table B is causing the problem. Thanks in advance.

[kaykays]

Is Table B also indexed on the Field ID?

[tyson.crouch]

Sorry, please forgive me.

My PHP is VERY rusty, however what you SHOULD do to speed up the processing time is call your DB connection outside of the for loop first off. This will dramatically reduce your query processing time.

Secondly, i would recommend that you use a VIEW to retrieve your data before processing. I've written up the script for you, though you may need to edit the PHP syntax.

$sql = $sql + "CREATE VIEW employee"
                      + "AS"
                      + "SELECT TableA.empl_fname, TableA.empl_lname, TableA.is_private, TableB.empl_uname, TableB.email_set"
                      + "FROM TableA"
                      + "INNER JOIN TableB"
                      + "WHERE"
                          + "TableA.is_private = 'N',"
		          + "TableA.empl_lname LIKE '".$empl_lname."'%,"
                          + "TableB.email_set = 'yes',"
                      + "ORDER BY TableA.empl_lname, TableA.empl_fname";

Once your view has been created, you may then call your view as you would any normal table.

SELECT * FROM employee

Also note that you do not have to create your view every time you use it. Create it once and continue one.