I have a database that is already in use and I have to improve the performance of the system that's using this database.
There are 2 major queries running about 1000 times in a loop and this queries have inner joins to 3 other tables each. This in turn is making the system very slow.
I tried actually to remove the query from the loop and fetch all the data only once and process it in PHP. But this is putting to much load on the memory (RAM) and the system is hanging if 2 or more clients try to use the system.
There is a lot of data in the tables even after removing the expired data .
I have attached the query below.
Can anyone help me with this issue ?
select * from inventory
where (region_id = 38 or region_id = -1)
and (tour_opp_id = 410 or tour_opp_id = -1)
and room_plan_id = 141 and meal_plan_id = 1 and bed_type_id = 1 and hotel_id = 1059
and FIND_IN_SET(supplier_code, 'QOA,QTE,QM,TEST,TEST1,MQE1,MQE3,PERR,QKT')
and ( ('2014-11-14' between from_date and to_date) )
order by hotel_id desc ,supplier_code desc, region_id desc,tour_opp_id desc,inventory.inventory_id desc
SELECT * ,pinfo.fri as pi_day_fri,pinfoadd.fri as pa_day_fri,pinfochld.fri as pc_day_fri
inner join profit_markup_info as pinfo on pinfo.profit_id = profit_markup.profit_markup_id
inner join profit_markup_add_info as pinfoadd on pinfoadd.profit_id = profit_markup.profit_markup_id
inner join profit_markup_child_info as pinfochld on pinfochld.profit_id = profit_markup.profit_markup_id
where profit_markup.hotel_id = 1059 and (`booking_channel` = 1 or `booking_channel` = 2)
and (`rate_region` = -1 or `rate_region` = 128)
and ( ( period_from <= '2014-11-14' and period_to >= '2014-11-14' ) )
ORDER BY profit_markup.hotel_id DESC,supplier_code desc, rate_region desc,operators_list desc, profit_markup_id DESC
Since we have not seen your
SHOW CREATE TABLES; and
EXPLAIN EXTENDED plan it is hard to give you 1 answer
But generally speaking in regard to your query "BTW I re-wrote below"
SELECT hotel_id, supplier_code, region_id, tour_opp_id, inventory_id FROM inventory WHERE region_id IN (38, -1) AND tour_opp_id IN (410, -1) AND room_plan_id IN (141, 1) AND bed_type_id IN (1, 1059) AND supplier_code IN ('QOA', 'QTE', 'QM', 'TEST', 'TEST1', 'MQE1', 'MQE3', 'PERR', 'QKT') AND ('2014-11-14' BETWEEN from_date AND to_date ) ORDER BY hotel_id DESC, supplier_code DESC, region_id DESC, tour_opp_id DESC, inventory_id DESC
Do not use
* to get all the columns. You should list the column that you really need. Using
* is just a lazy way of writing a query. limiting the columns will limit the data size that is being selected.
How often is the records in the inventory are being updates/inserted/delete? If not too often then you can use consider using
SQL_CACHE. However, caching a query will cause you problems if you use it and the inventory table is updated very often. In addition, to use query cache you must check the value of
query_cache_type on your server.
SHOW GLOBAL VARIABLES LIKE 'query_cache_type';. If this is set to "0" then the cache feature is disabled and
SQL_CACHE will be ignored. If it is set to 1 then the server will cache all queries unless you tell it not too using
NO_SQL_CACHE. If the option is set to 2 then MySQL will cache the query only where SQL_CACHE clause is used. here is documentation about query_cache_type
If you have an index on those following column in this order it will help you
(hotel_id, supplier_code, region_id, tour_opp_id, inventory_id)
ALTER TABLE inventory ADD INDEX (hotel_id, supplier_code, region_id, tour_opp_id, inventory_id);
If possible increase
sort_buffer_size on your server as most likely you issue here is that your are doing too much sorting.
As for the second query "BTW I re-wrote below"
SELECT *, pinfo.fri as pi_day_fri, pinfoadd.fri as pa_day_fri, pinfochld.fri as pc_day_fri FROM profit_markup INNER JOIN profit_markup_info AS pinfo ON pinfo.profit_id = profit_markup.profit_markup_id INNER JOIN profit_markup_add_info AS pinfoadd ON pinfoadd.profit_id = profit_markup.profit_markup_id INNER JOIN profit_markup_child_info AS pinfochld ON pinfochld.profit_id = profit_markup.profit_markup_id WHERE profit_markup.hotel_id = 1059 AND booking_channel IN (1, 2) AND rate_region IN (-1, 128) AND period_from <= '2014-11-14' AND period_to >= '2014-11-14' ORDER BY profit_markup.hotel_id DESC, supplier_code DESC, rate_region DESC, operators_list DESC, profit_markup_id DESC
Again eliminate the use of
* from your query
Make sure that the following columns have the same type/collation and same size.
pinfo.profit_id, profit_markup.profit_markup_id, pinfoadd.profit_id, pinfochld.profit_id and each one have to have an index on every table. If the columns have different types then MySQL will have to convert the data every time to join the records. Even if you have index it will be slower. Also, if those column are characters type (ie. VARCHAR()) make sure they are of the
CHAR() with a collation of
latin1_general_ci as this will be faster for finding ID, but if you are using INT() even better.
Use the 3rd and 4th trick I listed for the previous query
Try using STRAIGHT_JOIN "you must know what your doing here or it will bite you!" Here is a good thread about this When to use STRAIGHT_JOIN with MySQL
I hope this helps.