How to improve Mysql database performance without changing the db structure

Question

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
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` = 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

Answer 1

For the first query, I am not sure if you can do much (assuming you have already indexed the fields you are ordering by) apart from replacing the * with column names (Don't expect this to increase the performance drastically).

For the second query, before you go through the loop and put in selection arguments, you could create a view with all the tables joined and ordered then make a prepared statement to select from the view and bind arguments in the loop.

Also, if your php server and the database server are in two different places, it is better if you did the selection through a stored procedure in the database.

(If nothing works out, then memcache is the way to go... Although I have personally never done this)

Answer 2

Here you have increase query performance not an database performance.

For both queries first check index is available on WHERE and ON(Join) clause columns, if index is missing then you have to add index to improve query performance.

Check explain plane before create index.

If possible show me the explain plane of both query that will help us.

Answer 3

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

1. 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.

2. 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

3. 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);
   

4. 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

1. Again eliminate the use of * from your query

2. 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.

3. Use the 3rd and 4th trick I listed for the previous query

4. 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.