Php mysql queries run slow on wampserver 2.5
Posted by: lexer (---.73-185-103.dyn.dsl.cantv.net)
Date: August 14, 2015 04:29PM

I'm using wampserver 2.0b (It has apache 2.2.8, Mysql 5.0.51a and php 5.2.5) installed on windows 7 (64bits), I'm doing php-mysql nestle queries and run fast, I uninstall 2.0b version and installed wampserver 2.5 (32bit I couldn't install 64 bits), Now When I do the same queries on wampserver 2.5, these queries run slower than queries on version 2.0b. Please, Do the Php and Mysql that comes with wampserver 2.5 version requieres more CPU processing or Do I have to set up something?

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: RiggsFolly (---.as43234.net)
Date: August 14, 2015 04:51PM

Hello lexer,

I aam sorry but that question is almost impossible to answer as we are not clarevoyant and we are not looking over your shoulder.

I also have no idea what a nestle query is?

However if you would like to provide some more detailed information we will do our best to help, although once WAMPServer is installed and working, its kind of up to you to either know what you are doing with the software, or research it for yourself.
MySQL and PHP are well documented on their respective sites, see my signature for links to these sites.

---------------------------------------------------------------------------------------------
(Windows 7 Pro 32bit) (Wampserver 3.0.6 32bit)
<Apache 2.4.23/2.2.31> <PHP 7.1.2/7.0.16/5.6.28/5.5.38/5.4.45/5.3.29> <MySQL 5.7.10/5.6.17/5.5.28>
<MariaDB 10.1.21> <phpMyAdmin4.6.5.2> <MySQL Workbench 6.3.6.511>

Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin -- WAMPServer alternate Repo
-?-?-?- Backup your MySQL databases regularly Here is How dont regret it later! Yes even when developing -?-?-?-

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: lexer (---.73-185-103.dyn.dsl.cantv.net)
Date: August 14, 2015 05:52PM

Thanks for answer,

Basically, I've got 3 nestle queries,

Query 1 Gets Deparment
Query 2 Gets Extension (for every department)
Query 4 Gets calls calls duration and cost for every extension

This is just code (queries) for Wampserver 2.5 run slow:

$con = mysqli_connect("localhost","root","password","calls"winking smiley;
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

$query1 = "SELECT departament ".
"FROM departaments ".
"ORDER BY departaments.departament ";
$result1 = mysqli_query($con,$query1) or die(mysql_error());


while($row1 = mysqli_fetch_assoc($result1))
{
//Do query2
$query2 = "SELECT * ".
"FROM estructura ";
"ORDER BY estructura.Ext ";
$result2 = mysqli_query($con,$query2) or die(mysql_error());
//Loop Query2
while($row2 = mysqli_fetch_assoc($result2))
{

//Do Query4
$query4 = "SELECT iva.iva, registro.Ext, estructura.responsable, tarifas.numero, registro.duracion, tarifas.destino, tarifas.costo AS CostoX, estructura.departament, registro.numero, registro.Nombrenumero, registro.tipodestino, registro.TipoLlam, registro.duracionpbx, registro.fecha, registro.hora, ((registro.duracion*tarifas.costo)+ cargobase) AS Total ".
"FROM registro , estructura , tarifas, iva ".
"WHERE registro.Ext = estructura.Ext AND registro.IdLlam = tarifas.IdLlam AND registro.TipoLlam = '$Direccion' AND registro.tipodestino LIKE '$tipodestinof%' AND fecha BETWEEN '$calendar8M' AND '$calendar9M' AND hora BETWEEN '$horaini' AND '$horafin' AND registro.numero LIKE '$numerob%' AND registro.duracionpbx >= '$duracionbf' ORDER BY estructura.Ext, fecha, hora ";
$result4 = mysqli_query($con,$query4) or die(mysql_error());
// Print out the contents of each row into a table

while($row4 = mysqli_fetch_assoc($result4))
{
//Si estructura.Ext = registro.Ext
if ($row1['departament'] == $row2['departament'])// AND $row2['tipodestino '] == $row3['tipodestino'])
{
if ($row2['Ext'] == $row4['Ext'])
{


echo $row4['Ext'];
echo $row4['duracionpbx'];
echo $row4['Total'];


} //Close If Si estructura.Ext = registro.Ext
} //Close If Si departament = departament
} //Close Query4


}//Close Query2
}//Close Query1



This is just code (queries) for Wampserver 2.0b run faster:


$con = mysql_connect("localhost","root","password"winking smiley;
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("calls", $con);

//Do query1
$query1 = "SELECT departament ".
"FROM departaments ".
"ORDER BY departaments.departament ";
$result1 = mysql_query($query1) or die(mysql_error());


while($row1 = mysql_fetch_array($result1))
{
//Do Query2
$query2 = "SELECT * ".
"FROM estructura ";
"ORDER BY estructura.Ext ";
$result2 = mysql_query($query2) or die(mysql_error());
//Loop Query2
while($row2 = mysql_fetch_array($result2))
{

//Do Query4
$query4 = "SELECT iva.iva, registro.Ext, estructura.responsable, tarifas.numero, registro.duracion, tarifas.destino, tarifas.costo, estructura.departament, registro.numero, registro.Nombrenumero, registro.tipodestino, registro.TipoLlam, registro.duracionpbx, registro.fecha, registro.hora, ((registro.duracion*tarifas.costo)+ cargobase) AS Total ".
"FROM registro , estructura , tarifas, iva ".
"WHERE registro.Ext = estructura.Ext AND registro.IdLlam = tarifas.IdLlam AND registro.TipoLlam = '$Direccion' AND registro.tipodestino LIKE '$tipodestinof%' AND fecha BETWEEN '$calendar8M' AND '$calendar9M' AND hora BETWEEN '$horaini' AND '$horafin' AND registro.numero LIKE '$numerob%' AND registro.duracionpbx >= '$duracionbf' ORDER BY estructura.Ext, fecha, hora ";
$result4 = mysql_query($query4) or die(mysql_error());
// Print out the contents of each row into a table

while($row4 = mysql_fetch_array($result4))
{
//Si estructura.Ext = registro.Ext
if ($row1['departament'] == $row2['departament'])// AND $row2['tipodestino '] == $row3['tipodestino'])
{
if ($row2['Ext'] == $row4['Ext'])
{


echo $row4['Ext'];
echo $row4['duracionpbx'];
echo $row4['Total'];

} //Close If Si estructura.Ext = registro.Ext
} //Close If Si departament = departament
} //Close Query4

any ideas?

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: Otomatic (Moderator)
Date: August 14, 2015 06:31PM

Hi,

Don't use "localhost" as MySQL host. Use "127.0.0.1".

In wamp/bin/mysql/mysql5.x.y/my.ini file under the section [wampmysqld] add
#Added to reduce memory used (minimum is 400)
table_definition_cache = 600

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.9/7.0.23/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.9/7.0.23/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: RiggsFolly (---.as43234.net)
Date: August 14, 2015 09:33PM

Oh?

NESTLE = NESTED


FOFL LOL HaHaHa

Sorry I am not laughing at you, but it made my day. Thank you

---------------------------------------------------------------------------------------------
(Windows 7 Pro 32bit) (Wampserver 3.0.6 32bit)
<Apache 2.4.23/2.2.31> <PHP 7.1.2/7.0.16/5.6.28/5.5.38/5.4.45/5.3.29> <MySQL 5.7.10/5.6.17/5.5.28>
<MariaDB 10.1.21> <phpMyAdmin4.6.5.2> <MySQL Workbench 6.3.6.511>

Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin -- WAMPServer alternate Repo
-?-?-?- Backup your MySQL databases regularly Here is How dont regret it later! Yes even when developing -?-?-?-

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: lexer (---.73-185-103.dyn.dsl.cantv.net)
Date: August 14, 2015 09:36PM

Hahaha, I'm sorry, I meant nested

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: lexer (---.73-185-103.dyn.dsl.cantv.net)
Date: August 14, 2015 09:59PM

Hi

Thanks, for your answers

I changed "localhost" for "127.0.0.1".

And at wamp/bin/mysql/mysql5.x.y/my.ini file under the section [wampmysqld] I added

#Added to reduce memory used (minimum is 400)
table_definition_cache = 600

But Still It is faster wampserver 2.0b than 2.5

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: Otomatic (Moderator)
Date: August 15, 2015 02:10PM

Hi,

Your Query:
$query4 = "SELECT iva.iva, registro.Ext, estructura.responsable, tarifas.numero, registro.duracion, tarifas.destino, tarifas.costo AS CostoX, estructura.departament, registro.numero, registro.Nombrenumero, registro.tipodestino, registro.TipoLlam, registro.duracionpbx, registro.fecha, registro.hora, ((registro.duracion*tarifas.costo)+ cargobase) AS Total ".
"FROM registro , estructura , tarifas, iva ".
"WHERE registro.Ext = estructura.Ext AND registro.IdLlam = tarifas.IdLlam AND registro.TipoLlam = '$Direccion' AND registro.tipodestino LIKE '$tipodestinof%' AND fecha BETWEEN '$calendar8M' AND '$calendar9M' AND hora BETWEEN '$horaini' AND '$horafin' AND registro.numero LIKE '$numerob%' AND registro.duracionpbx >= '$duracionbf' ORDER BY estructura.Ext, fecha, hora ";

This query is very badly written.
As you use multiple tables with relationships between them, you should use "JOIN".
You should also use alias for table names like this:
SELECT I.iva, R.Ext, E.responsable, T.numero, R.duracion, T.destino,
T.costo AS CostoX, E.departament, R.numero, R.Nombrenumero,
R.tipodestino, R.TipoLlam, R.duracionpbx, R.fecha, R.hora,
((R.duracion*T.costo)+ cargobase) AS Total ".
"FROM registro AS R, estructura AS E, tarifas AS T, iva AS I ".
As you use multiple tables, you should also always specify which table a column belongs, for example for cargobase, fecha, hora ; because if you do not specify, MySQL will scan all the tables to find to which this column belongs, and that takes time and it can cause problems if the same column name is found in several tables.

On the one hand, say that it's longer with Wampserver 2.5 qu'aver WampServer 2.0d is entirely subjective fact.
Did you perform reliable measurements queries times in every environment?

Moreover, switching from Apache 2.2.28, PHP 5.2.5, MySQL 5.0.51 to Apache 2.4.9, PHP 5.5.12 and MySQL 5.6.25 is a great march of evolution.
Have you optimized the indexes of your tables with new versions?
Indeed you use "LIKE" on several columns and if the indexes of these do not exist or are outdated, MySQL will have to scan the entire column of values and it will take much time.
Have you launched "mysql_upgrade" on your databases with the new version of MySQl to bring them into line with this new version?

To be able to say with certainty that it takes longer in 2.5 than in 2.0d, it is imperative to have answered yes to all these questions.

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.9/7.0.23/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.9/7.0.23/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: lexer (---.73-185-103.dyn.dsl.cantv.net)
Date: August 17, 2015 04:20PM

Thanks for your answer Otomatic.

Your recomendation:

"As you use multiple tables, you should also always specify which table a column belongs"


For example in the following query:

$query4 = "SELECT iva.iva, registro.Ext, estructura.responsable, tarifas.numero, registro.duracion, tarifas.destino, tarifas.costo AS CostoX, estructura.departament, registro.numero, registro.Nombrenumero, registro.tipodestino, registro.TipoLlam, registro.duracionpbx, registro.fecha, registro.hora, ((registro.duracion*tarifas.costo)+ cargobase) AS Total ".
"FROM registro , estructura , tarifas, iva ".

am I not telling the column iva belongs to table iva (SELECT iva.iva FROM iva) ?

Or Please, What is the way to do it?

you said:

"Did you perform reliable measurements queries times in every environment?"


Yes, for the same database I did queries with Wampserver 2.0b and 2.5 and I did measurements of queries time, the queries with wampserver 2.0b were faster than with 2.5

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: RiggsFolly (---.as43234.net)
Date: August 17, 2015 04:32PM

Did you do any optimisation of your old MYSQL environment?

Have you done any optimisation of MYSQL on the new version?

---------------------------------------------------------------------------------------------
(Windows 7 Pro 32bit) (Wampserver 3.0.6 32bit)
<Apache 2.4.23/2.2.31> <PHP 7.1.2/7.0.16/5.6.28/5.5.38/5.4.45/5.3.29> <MySQL 5.7.10/5.6.17/5.5.28>
<MariaDB 10.1.21> <phpMyAdmin4.6.5.2> <MySQL Workbench 6.3.6.511>

Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin -- WAMPServer alternate Repo
-?-?-?- Backup your MySQL databases regularly Here is How dont regret it later! Yes even when developing -?-?-?-

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: Otomatic (Moderator)
Date: August 17, 2015 05:57PM

Hi,

> am I not telling the column iva belongs to table iva (SELECT iva.iva FROM iva) ?
> Or Please, What is the way to do it?
I don't speak about iva.iva but about:
- cargobase -> ((registro.duracion*tarifas.costo)+ cargobase) AS Total ".
- fecha -> AND fecha BETWEEN '$calendar8M' AND '$calendar9M'
- hora -> AND hora BETWEEN '$horaini'
- fecha & hora -> ORDER BY estructura.Ext, fecha, hora ";
For these columns, you do not specify which table they originate.
The easiest way is to assign aliases to tables in the form of a single letter, as I have shown in my example.

It is not the role of the WHERE clause to give the relationships between tables as I have said, you should use table joins.
For example, you use:
"WHERE registro.Ext = estructura.Ext
this should done with
FROM registro AS R
INNER JOIN estructura AS E
  ON R.Ext = E.Ext
and so on for the other tables. Warning: it's only an example.

> Yes, for the same database I did queries with Wampserver 2.0b and 2.5 and I did measurements of queries time,
How ?
The way to do reliable measurements is like this:
$sql = "my query";
$q_start = microtime(true);
$result = mysqli_query($sql) or die(mysql_error());
$q_stop = microtime(true);
saved_queries[] = array($sql, sprintf('%.5f', $q_stop - $q_start));
Then at the end of the script, echo the content of the array saved_queries. The measurement of time queries should not include processing time of the result by PHP.

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.9/7.0.23/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.9/7.0.23/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Php mysql queries run slow on wampserver 2.5
Posted by: RiggsFolly (---.as43234.net)
Date: August 18, 2015 11:27AM

Hi

Something that both I and possibly Oto missed is that you have also changed your code to use the MYSQLI_ database access extension from the original MYSQL_ database access extension. This si of course a good thing to do.

So this is not just a difference between 2 versions of MYSQL or PHP but the use of a completely different PHP extension.


It would be interesting to know some REAL metrics, eg

timings of the old code (mysql_) running on the new MYSQL
timings of the new code (mysqli) running on the new MYSQL

Then possibly we could get a better idea of where to look.


I do have to agree with Oto, that your queries should also be refactored.

---------------------------------------------------------------------------------------------
(Windows 7 Pro 32bit) (Wampserver 3.0.6 32bit)
<Apache 2.4.23/2.2.31> <PHP 7.1.2/7.0.16/5.6.28/5.5.38/5.4.45/5.3.29> <MySQL 5.7.10/5.6.17/5.5.28>
<MariaDB 10.1.21> <phpMyAdmin4.6.5.2> <MySQL Workbench 6.3.6.511>

Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin -- WAMPServer alternate Repo
-?-?-?- Backup your MySQL databases regularly Here is How dont regret it later! Yes even when developing -?-?-?-

Options: ReplyQuote


Sorry, only registered users may post in this forum.