How do i reduce this query runtime?

Question

I tried three way to reduce it's runtime but i can't, still it takes 97.00456 seconds, if any one can please suggest me a solution.

SELECT r.id, r.first_name, r.phone, r.checkin_id, r.device_id, replace(c.`name`,' ','') as device, r.model_id,
    cv.`name` as model, r.color_id, cvc.`name` as color, r.network_id, cn.`name` as network, r.problem_id, cp.`name` as problem,
    #get pid by concat device detail
    IFNULL(
        (SELECT id 
        FROM product 
        WHERE 
            #if not admin
            #store_id=$input_by AND 
            #if not admin 
            name=concat(replace(c.`name`,' ',''),', ',cv.`name`,' - ',cp.`name`) 
        ORDER BY id DESC 
        LIMIT 1)
        ,
        (SELECT id 
        FROM product 
        WHERE 
            #if not admin
            #store_id=$inout_by AND 
            #if not admin 
            name=concat(replace(c.`name`,' ',''),' , ',cv.`name`,' - ',cp.`name`) 
        ORDER BY id DESC 
        LIMIT 1)
    ) AS pid,
    #get pid by concat device detail END
    IFNULL(
        (SELECT id 
        FROM coustomer as cus 
        WHERE cus.firstname=r.first_name AND cus.phone=r.phone 
        LIMIT 1)
        ,
        0
    ) as cid,
    pc.invoice_id as invoice_id,
    #(SELECT count(id) FROM invoice WHERE invoice_id=IFNULL((SELECT invoice_id FROM pos_checkin WHERE checkin_id=r.checkin_id),0) LIMIT 1) AS invoice_status,
    SUM(ip.amount) as paid,
    r.date,
    r.input_by,
    r.status 
FROM checkin_request as r
LEFT JOIN pos_checkin as pc on pc.checkin_id=r.checkin_id
LEFT JOIN invoice_payment as ip on ip.invoice_id=pc.invoice_id
LEFT JOIN checkin as c ON c.id=r.device_id
LEFT JOIN checkin_version as cv ON cv.id=r.model_id
LEFT JOIN checkin_version_color as cvc ON cvc.id=r.color_id
LEFT JOIN checkin_network as cn ON cn.id=r.network_id
LEFT JOIN checkin_problem as cp ON cp.id=r.problem_id 
WHERE r.checkin_id IN (
    SELECT crt.checkin_id 
    FROM checkin_request_ticket as crt
    )
GROUP BY r.checkin_id 
ORDER BY id DESC
LIMIT 5 

i tried using left join , tried with nested query , tried with sub query but failed. Please leave a solution if anyone does.

Answer 1

#1: Using indexes

MySQL allows you to index database tables, making it possible to quickly seek to records without performing a full table scan first and thus significantly speeding up query execution. You can have up to 16 indexes per table, and MySQL also supports multi-column indexes and full-text search indexes.

#2: Optimizing query performance

When analyzing query performance, it's also useful to consider the EXPLAIN keyword. This keyword, when placed in front of a SELECT query, describes how MySQL intends to execute the query and the number of rows it will need to process to successfully deliver a result set.

Altering Index Buffer Size (key_buffer)

This variable controls the size of the buffer used when handling table indices (both read and write operations). The MySQL manual recommends that this variable be increased "to as much as you can afford" to ensure you get the best performance on indexed tables, and recommends a value equivalent to about 25 percent of the total system memory. This is one of the more important MySQL configuration variables and if you're interested in optimizing and improving performance, trying different values for the key_buffer_size variable is a good place to start.
Altering Table Buffer Size (read_buffer_size)
When a query requires a table to be scanned sequentially, MySQL allocates a memory buffer to this query. The read_buffer_size variable controls the size of this buffer. If you find that sequential scans are proceeding slowly, you can improve performance by increasing this value, and hence the size of the memory buffer.

Setting The Number Of Maximum Open Tables (table_cache) This variable controls the maximum number of tables MySQL can have open at any one time, and thus controls the server's ability to respond to incoming requests. This variable is closely related to the max_connections variables — increasing this value allows MySQL to keep a larger number of tables open, just as increasing max_connections increases the number of allowed connections. Consider altering this value if you have a high-volume server which receives queries on multiple different databases and tables.