I've got some problems with the following code in my PHP/MySQL application. It works well and takes about 3-4 seconds, but first execution (per session) takes about 2 minutes. I think because there's some automated-cache mechanisms. There's a method to speed up the first execution? I've got the root access on this MySQL server, but I can't modify the DB structure.
The application is visible here http://hotel.crosstourpoint.eu/, and the slow script is that http://hotel.crosstourpoint.eu/ajax/html_hotel_details.php. To check out it search something in the main box. Example: type "Milano" and click "Cerca", click on the option "Milano", select start date and end date ("Giorno di arrivo - Giorno di partenza") and click again "Cerca". The Info (I) icon opens the slow script with an ajax call.
Thanks.
Code
<?php
// open mysqli connection
$mysqli = new mysqli('localhost', 'hotelbeds', 'import', 'hotelbeds');
if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); }
$code = (int) $_REQUEST['code'];
$h = array();
// hotel position
$request = '
SELECT
NAME, LATITUDE, LONGITUDE
FROM
HOTELS
WHERE
HOTELCODE = ' . $code . ' ';
$stmt = $mysqli->prepare($request);
$stmt->execute();
$stmt->bind_result( $h['name'], $h['latitude'], $h['longitude'] );
$stmt->fetch();
$stmt->close();
unset($stmt);
unset($request);
// loading descriptions
$request = '
SELECT
HotelFacilities, HotelHowToGetThere, HotelComments
FROM
HOTEL_DESCRIPTIONS
WHERE
HotelCode = ' . $code . '
AND
LanguageCode = "' . HB_LANGCODE . '" ';
$stmt = $mysqli->prepare($request);
$stmt->execute();
$stmt->bind_result( $h['facilities'], $h['hotelhowtogetthere'], $h['comments'] );
$stmt->fetch();
$stmt->close();
unset($stmt);
unset($request);
// hotel images
$request = '
SELECT
IMAGEPATH
FROM
HOTEL_IMAGES
WHERE
HOTELCODE = ' . $code . ' ';
$stmt = $mysqli->prepare($request);
$stmt->execute();
$stmt->bind_result( $imagepath );
$images = array();
while( $stmt->fetch() ) array_push( $images, $imagepath );
$stmt->close();
unset($stmt);
unset($request);
Tables structure
HOTELS: about 50.000 rows
HOTELS_DESCRIPTIONS about 600.000 rows
HOTELS_IMAGES: about 180.000 rows
CREATE TABLE `HOTELS` (
`HOTELCODE` varchar(8) collate utf8_spanish_ci NOT NULL,
`NAME` varchar(50) collate utf8_spanish_ci NOT NULL,
`CATEGORYCODE` varchar(5) collate utf8_spanish_ci NOT NULL,
`DESTINATIONCODE` varchar(3) collate utf8_spanish_ci NOT NULL,
`ZONECODE` varchar(8) collate utf8_spanish_ci default NULL,
`CHAINCODE` varchar(5) collate utf8_spanish_ci default NULL,
`LICENCE` varchar(15) collate utf8_spanish_ci default NULL,
`LATITUDE` varchar(45) collate utf8_spanish_ci default NULL,
`LONGITUDE` varchar(45) collate utf8_spanish_ci default NULL,
PRIMARY KEY (`HOTELCODE`),
KEY `HOTELS_CATEGORIES_FK` (`CATEGORYCODE`),
KEY `HOTELS_ZONES_FK` (`ZONECODE`),
CONSTRAINT `HOTELS_ZONES_FK` FOREIGN KEY (`ZONECODE`) REFERENCES `ZONES` (`ZONECODE`) ON DELETE CASCADE,
CONSTRAINT `HOTELS_CATEGORIES_FK` FOREIGN KEY (`CATEGORYCODE`) REFERENCES `CATEGORIES` (`CategoryCode`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Hotels'
CREATE TABLE `HOTEL_DESCRIPTIONS` (
`HotelCode` varchar(8) collate utf8_spanish_ci NOT NULL,
`LanguageCode` varchar(3) collate utf8_spanish_ci NOT NULL,
`HotelFacilities` varchar(2000) collate utf8_spanish_ci default NULL,
`HotelLocationDescription` varchar(2000) collate utf8_spanish_ci default NULL,
`HotelRoomDescription` varchar(2000) collate utf8_spanish_ci default NULL,
`HolelSportDescription` varchar(2000) collate utf8_spanish_ci default NULL,
`HotelMealsDescription` varchar(2000) collate utf8_spanish_ci default NULL,
`HotelPaymentMethods` varchar(2000) collate utf8_spanish_ci default NULL,
`HotelHowToGetThere` varchar(2000) collate utf8_spanish_ci default NULL,
`HotelComments` varchar(2000) collate utf8_spanish_ci default NULL,
PRIMARY KEY (`HotelCode`,`LanguageCode`),
KEY `HOTEL_DESCRIPTIOS_LANGUAGES_FK` (`LanguageCode`),
CONSTRAINT `HOTEL_DESCRIPTIOS_LANGUAGES_FK` FOREIGN KEY (`LanguageCode`) REFERENCES `LANGUAGES` (`LANGUAGECODE`) ON DELETE CASCADE,
CONSTRAINT `HOTEL_DESCRIPTIOS_HOTELS_FK` FOREIGN KEY (`HotelCode`) REFERENCES `HOTELS` (`HOTELCODE`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Hotel_Descriptions'
CREATE TABLE `HOTEL_IMAGES` (
`HOTELCODE` varchar(8) collate utf8_spanish_ci NOT NULL,
`IMAGECODE` varchar(3) collate utf8_spanish_ci NOT NULL,
`ORDER_` varchar(5) collate utf8_spanish_ci NOT NULL,
`VISUALIZATIONORDER` varchar(5) collate utf8_spanish_ci default NULL,
`IMAGEPATH` varchar(2000) collate utf8_spanish_ci NOT NULL,
PRIMARY KEY (`HOTELCODE`,`IMAGECODE`,`ORDER_`),
KEY `HOTEL_IMAGES_IMAGE_TYPES_FK` (`IMAGECODE`),
CONSTRAINT `HOTEL_IMAGES_IMAGE_TYPES_FK` FOREIGN KEY (`IMAGECODE`) REFERENCES `IMAGE_TYPES` (`IMAGECODE`) ON DELETE CASCADE,
CONSTRAINT `HOTEL_IMAGES_HOTELS_FK` FOREIGN KEY (`HOTELCODE`) REFERENCES `HOTELS` (`HOTELCODE`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Hotels_Images'
Additional informations (edit):
Ubuntu 64bit 8.04.2 Linux hostname
2.6.24-23-server #1 SMP Wed Apr 1 22:14:30 UTC 2009 x86_64 GNU/Linux
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2
innodb_buffer_pool_size 512
Explains:
>> EXPLAIN SELECT NAME, LATITUDE, LONGITUDE FROM HOTELS WHERE HOTELCODE = 136224
id | select_type | table | type | possibile_keys | key key_len | ref | rows
1 SIMPLE HOTELS ALL PRIMARY 47373 Using where
>> EXPLAIN SELECT HotelFacilities, HotelHowToGetThere, HotelComments FROM HOTEL_DESCRIPTIONS WHERE HotelCode = 136224 AND LanguageCode = "ITA"
id | select_type | table | type | possibile_keys | key key_len | ref | rows
1 SIMPLE HOTEL_DESCRIPTIONS ref PRIMARY,HOTEL_DESCRIPTIOS_LANGUAGES_FK HOTEL_DESCRIPTIOS_LANGUAGES_FK 11 const 75378 Using where
>> EXPLAIN SELECT IMAGEPATH FROM HOTEL_IMAGES WHERE HOTELCODE = 136224
id | select_type | table | type | possibile_keys | key key_len | ref | rows
1 SIMPLE HOTEL_IMAGES ALL PRIMARY 158786 Using where
You say you cannot change the DB structure, this is most unfortunate because I have mostly DB-structure advice to give...
Join the queries
Your queries are about as tight as they're going to get.
You might want to put them all in one big query like:
$request = 'SELECT
h.NAME, h.LATITUDE, h.LONGITUDE
,hd.HotelFacilities, hd.HotelHowToGetThere, hd.HotelComments
,hi.ImagePath
FROM HOTELS
INNER JOIN HOTEL_DESCRIPTIONS hd ON (h.Hotelcode = hd.Hotelcode)
INNER JOIN HOTEL_IMAGES hi ON (h.Hotelcode = hi.Hotelcode)
WHERE HD.Hotelcode = "' .$code. '" AND HD.LanguageCode = "' . HB_LANGCODE . '" ';
Optimize the cache
This will make sure more of them fit into the query-cache.
The delay on the first query is caused by a cold query cache,
see: http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
For more info on this. (Note that the article states that prepared statements are not cached, this is no longer true; as of 5.1.17 prepared statements are cached.)
Primary key
Make field hotelcode integer. Make it an autoincrement for table hotel only.
Hotelcode is an int (see: $code = (int) $_REQUEST['code'];)
So why make it a varchar?
Use char(x) for small values of x, not varchar
Don't use a varchar(3), use a char(3). The varchar(3) is variable length and takes extra processing time to figure out the length of the string, with only 3 chars there's no real space saving.
I'd recommend using char(x) for x < 8.
Foreign keys
Try and use only integers for foreign keys, they work faster and foreign keys are usually linked to some other table's primary key (PK), which should be integer anyway (see point above).
InnoDB and primary keys
In InnoDB the primary key is attached to all indexes, so making the primary key short speeds up every insert, update and select.
From: http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html
Each secondary index record also contains all the primary key fields defined for the clustered index key that are not in the secondary index. If any of these primary key fields are variable length, the record header for each secondary index will have a variable-length part to record their lengths, even if the secondary index is defined on fixed-length columns.
PK should not be a composite key
For tables that have a composite primary key, kill that and replace it with a autoincrement integer primary key (named id or something like that). This is because innoDB stores a copy of the PK in every index B+tree (see point above).
Replace the current primary key with a unique key, to make sure that no hotel has 2 descriptions in the same language etc..