[RESOLVED] Speeding up results table

Square1

Hi all:

Here is the FROM portion of a query:

FROM
Events
INNER JOIN Players ON Players.plID = Events.plID
INNER JOIN Promoters ON Promoters.prID = Events.prID
INNER JOIN PlayerDetails ON PlayerDetails.plID = Players.plID
INNER JOIN Sports ON Sports.SportCode =  Players.SportCode
INNER JOIN Locations ON Locations.loID = Events.loID

It is taking about 15 seconds for the results to appear. When I remove

INNER JOIN PlayerDetails ON PlayerDetails.plID = Players.plID

the time drops down to about 1 second.

PlayerDetails has about 40,000 records and Players has about 20,000 records. Both tables are indexed on "plID' and both have primary keys.

Is there anything else I can do to speed the results up or due to the size of both tables, is this the best it is????

Thank you!

johanafm

First off, the previous question you marked as resolved probably isn't. See the previous thread for a reply to that issue.

For this question, prepend EXPLAIN and post its output.

Square1

johanafm;10989632 wrote:
First off, the previous question you marked as resolved probably isn't. See the previous thread for a reply to that issue.

For this question, prepend EXPLAIN and post its output.

Uggghhh! I cannot get EXPLAIN to work...nothing appears on the page (I am new with this, first time trying it)

For example...the following simple query brings up a blank page

<?php

			require($_SERVER['DOCUMENT_ROOT'].'/inc/dbconn_RW.php');

	if ($result = mysqli_query($link, "
		 EXPLAIN SELECT
			Events.player
		FROM 
			Events
	")){
	$row = mysqli_fetch_array($result, MYSQLI_ASSOC);	
	echo $row["player"];
	mysqli_free_result($result);
	mysqli_close($link);
	}

?>

johanafm

Square1;10989927 wrote:

I cannot get EXPLAIN to work

if ($result = mysqli_query($link, "EXPLAIN SELECT Events.player FROM Events"))
{
	$row = mysqli_fetch_array($result, MYSQLI_ASSOC);	
	echo $row["player"];
}

The reason is that EXPLAIN doesn't return the data that the query would do without the EXPLAIN keyword would. It returns information on possible keys to use, keys used, number of expected rows retrieved in each table etc. However, you really shouldn't get a blank page. You should get a page with an error message (undefined index 'player'). Or if in a production environment, it should go in the error log. Check your php.ini for error_reporting, log_errors and display_errors.

If you do not know what an array contains, use print_r. If viewing the results in a browser, wrap the output from print_r in a <pre> element to preserve whitespaces for readability.

Square1

johanafm;10989933 wrote:
The reason is that EXPLAIN doesn't return the data that the query would do without the EXPLAIN keyword would. It returns information on possible keys to use, keys used, number of expected rows retrieved in each table etc. However, you really shouldn't get a blank page. You should get a page with an error message (undefined index 'player'). Or if in a production environment, it should go in the error log. Check your php.ini for error_reporting, log_errors and display_errors.

If you do not know what an array contains, use print_r. If viewing the results in a browser, wrap the output from print_r in a <pre> element to preserve whitespaces for readability.

I apologize. When leaving the "echo" in do get the error message. I had removed the echo.

But I am lost. How do I get EXPLAIN to show me the data? Also, I am on a shared MySQL DB so I don't believe I have access to .ini info.

johanafm

Square1;10989937 wrote:
How do I get EXPLAIN to show me the data?

printf('<pre>%s</pre>', print_r($row, 1));

Square1;10989937 wrote:
Also, I am on a shared MySQL DB so I don't believe I have access to .ini info.

I've never used shared hosting, so I don't know what is and isn't available. But I would guess that you do have access to php.ini, since it would make sense for each user to have his or her own php.ini file. Otherwise, it might be possible to set these values from an .htaccess file

I believe this is how you do it from .htaccess. And note that display_errors should be turned off in a production environment. Also note that the value of display_errors will be different depending on your PHP version. Up to 5.2.4, the value is 1 for on, 0 for off. Later PHP versions take a file descriptor instead, i.e. stderr or stdout.

php_value error_reporting E_ALL
php_value log_errors /path/to/logfile
php_value display_errors stdout

Square1

Thank you!

So now I have the info and it pretty much as I expected. iot is the following that is the culprit but how do I remedy this as it is already indexed?

Array
(
[id] => 1
[select_type] => SIMPLE
[table] => PlayerDetails
[type] => index
[possible_keys] =>
[key] => plID
[key_len] => 43
[ref] =>
[rows] => 43931
[Extra] => Using where; Using index; Distinct

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => Events
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 647
    [Extra] => Using where; Using temporary; Using filesort
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => Promoters
    [type] => ref
    [possible_keys] => prID
    [key] => prID
    [key_len] => 123
    [ref] => func
    [rows] => 1
    [Extra] => Using where
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => Locations
    [type] => ref
    [possible_keys] => loID
    [key] => loID
    [key_len] => 123
    [ref] => func
    [rows] => 1
    [Extra] => Using where
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => Players
    [type] => ref
    [possible_keys] => plID
    [key] => plID
    [key_len] => 123
    [ref] => func
    [rows] => 1
    [Extra] => Using where
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => Sports
    [type] => ref
    [possible_keys] => SportCode
    [key] => SportCode
    [key_len] => 152
    [ref] => SportCode
    [rows] => 1
    [Extra] => Using where
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => PlayerDetails
    [type] => index
    [possible_keys] => 
    [key] => plID
    [key_len] => 43
    [ref] => 
    [rows] => 43931
    [Extra] => Using where; Using index; Distinct

Square1

GOT IT! I switched around an INNER JOIN connection and it worked!!!

Thank you johanafm for a great lesson in DBing!!!