Cliff Cliff - 1 year ago 56
MySQL Question

How to improve Mysql database performance without changing the db structure

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 ( ('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 Source

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"

    hotel_id, supplier_code, region_id, tour_opp_id, inventory_id
    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 )
    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"

    *, pinfo.fri as pi_day_fri,
    pinfoadd.fri as pa_day_fri, 
    pinfochld.fri as pc_day_fri
    profit_markup_info AS pinfo ON pinfo.profit_id = profit_markup.profit_markup_id
    profit_markup_add_info AS pinfoadd ON pinfoadd.profit_id = profit_markup.profit_markup_id
    profit_markup_child_info AS pinfochld ON pinfochld.profit_id = profit_markup.profit_markup_id
    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'
    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.