Query execution time issue

Question

I have some trouble with the execution time of a script. The query takes a lot of time. This is the query:

select avg(price) from voiture where duration<30 AND make="Audi" AND model="A4"

+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | voiture | ALL | NULL | NULL | NULL | NULL | 1376949 | Using where |
+---+---+---+---+---+---+---+---+---+---+

select price from voiture where duration<30 AND make="Audi" AND model="A4"
+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | voiture | ALL | NULL | NULL | NULL | NULL | 1376949 | Using where |
+---+---+---+---+---+---+---+---+---+---+

This query take around 2 seconds to be executed on the phpMyAdmin interface. I tried to see what the issue was and removing the avg function makes the query lasts around 0.0080 seconds.
I asked myself how long it would take to make the calculate the avg in the php script, but the query with or withouth avg takes around 2 seconds both.
So I decided to take all the values of my table and make the process in the script, so I use this query:

select * from voiture where duration<30 AND make="Audi" AND model="A4"

+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | voiture | ALL | NULL | NULL | NULL | NULL | 1376949 | Using where |
+---+---+---+---+---+---+---+---+---+---+

On the phpMyAdmin interface, it takes 0.0112 seconds. But in my php script, it takes 25 seconds !

$timestart2=microtime(true);
$querysec='select * from voiture where duration<30 AND make="Audi" AND model="A4"';
$requestsec=mysql_query($querysec) or die(mysql_error());
$timeend2=microtime(true);
$time2=$timeend2-$timestart2;
$page_load_time2 = number_format($time2, 9);

Here is the table structure:

CREATE TABLE `voiture` (
  `carid` bigint(9) NOT NULL,
  `serviceid` bigint(9) NOT NULL,
  `service` varchar(256) NOT NULL,
  `model` varchar(256) DEFAULT NULL,
  `gearingType` varchar(256) DEFAULT NULL,
  `displacement` int(5) DEFAULT NULL,
  `cylinders` int(2) DEFAULT NULL,
  `fuel` varchar(32) DEFAULT NULL,
  `mileage` int(7) DEFAULT NULL,
  `existFlag` tinyint(1) DEFAULT NULL,
  `lastUpdate` date DEFAULT NULL,
  `version` varchar(256) DEFAULT NULL,
  `bodyType` varchar(256) DEFAULT NULL,
  `firstRegistration` date DEFAULT NULL,
  `powerHp` int(4) DEFAULT NULL,
  `powerKw` int(4) DEFAULT NULL,
  `vat` varchar(256) DEFAULT NULL,
  `price` decimal(12,2) DEFAULT NULL,
  `duration` int(3) DEFAULT NULL,
  `pageUrl` varchar(256) DEFAULT NULL,
  `carImg` varchar(256) DEFAULT NULL,
  `color` varchar(256) DEFAULT NULL,
  `doors` int(1) DEFAULT NULL,
  `seats` int(1) DEFAULT NULL,
  `prevOwner` int(1) DEFAULT NULL,
  `co2` varchar(256) DEFAULT NULL,
  `consumption` varchar(256) DEFAULT NULL,
  `gears` int(1) DEFAULT NULL,
  `equipment` varchar(1024) NOT NULL,
  `make` varchar(256) NOT NULL,
  `country` varchar(3) NOT NULL
)

There's an index on carid and serviceid
Why does my query takes so long to be executed ? Is there a way it can be improved ?
Why is the execution time different from phpMyAdmin and my php script ?

You should provide us more information. What is your table structure, what are you indexing? You could try running an `EXPLAIN` on the query, that should clear up some things. If you don't understand it's output, add it to your question, it would also help us diagnoze the problem. — Adam Baranyai, Jun 01 '16 at 09:09
How many rows are returned in total with: `select * from voiture where duration<30 AND make="Audi" AND model="A4"` ? — beingalex, Jun 01 '16 at 09:09
Please dont use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the `PDO` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) its really pretty easy — RiggsFolly, Jun 01 '16 at 09:12
Did you try `SELECT price from .....` If you only want the avg of price only retrieve `price` — RiggsFolly, Jun 01 '16 at 09:15
@AdamBaranyai I edited my question with more informations, thx for your help — Nevi, Jun 01 '16 at 09:34
How large is the table? You are not using indexes when searching. Try adding an index on the `make` field dor example, and see if it fastens things up a bit, or not — Adam Baranyai, Jun 01 '16 at 09:43

vp_arth · Accepted Answer · 2016-06-01T09:54:29.007

1

On the phpMyAdmin interface, it takes 0.0112 seconds. But in my php script, it takes 25 seconds!

phpMyAdmin interface adds LIMIT to each query. By default it's LIMIT 30.

To decrease time of your aggregate query you need to create indexes for each condition you use(or one composite index, may be).

So, try to create indexes for your model, make and duration fields.

Also, your table is too denormalized. You can to create pair of table to normalize it for a bit.
Ex: Vendors(id, name), Models(id, name) and modify your voiture to have vendor_id/model_id fields instead of text make/model.

Then your initial query will look like:

select avg(t.price) from voiture t
INNER JOIN Models m ON m.id = t.model_id
INNER JOIN Vendors v ON v.id = t.vendor_id
where t.duration<30 AND v.name="Audi" AND m.name="A4"

It will scan light lookup tables for text matches and operate with your heavy table with indexed ids.

edited Jun 01 '16 at 09:54

answered Jun 01 '16 at 09:42

vp_arth

14,461
4
37
66

good answer, I added the three index in one composite index, the execution time went from 25 seconds to 1.5 seconds ! I already have a "make" and "model" table but the id of a row is the name of the make/model, that's why I use make="Audi". – Nevi Jun 01 '16 at 11:13
also, I normally use much more parameters than juste the make and the model (almost all the table fields in fact), is it a good thing to put all the field in a composite index ? – Nevi Jun 01 '16 at 11:14
it depends. It will too fat index and search inside of it has no profit against raw table full-scan. Textual primary keys(you use `varchar(256)`) is bad idea too. – vp_arth Jun 01 '16 at 13:56

score 0 · Answer 2 · answered Jun 01 '16 at 09:10

There are many possible solution, first thing you can do is create index at DB level this can also improve your execution time.

second this check server, there may be some processes which would occupying your server resources, and making your server slow.

Amit Ray · Answer 3 · 2016-06-01T10:04:29.870

0

You can make it faster using GROUP BY like

select AVG(price) from voiture where duration<30 AND make="Audi" AND model="A4" GROUP BY make

Also you can add an index for make column

ALTER TABLE `voiture` ADD INDEX `make` (`make`)

edited Jun 01 '16 at 10:04

answered Jun 01 '16 at 09:26

Amit Ray

3,445
2
19
35

i thought about it but in my php script, select price and select avg(price) takes the same time – Nevi Jun 01 '16 at 09:35
I have changed the answer. Please check if this works. @Nevi – Amit Ray Jun 01 '16 at 09:58

Query execution time issue

3 Answers3