What type of index should I add on multiple request_id in mysql
The data is taking too long to load when I search with the specified date. There are two tables in my project. One table has unique records and I added a unique index to "request_id" and a primary index to automatically increment the "id". another table has multiple records with request_id and in this one I only added the primary index to automatically increment the "id". Now I have to search all of these records through a join in both tables to check the count for each "request_id". I am using the following query: -
SELECT
m.id,m.request_id as id,count(m.request_id) as count,m.reqtype,m.request_time,w.status as status,w.updated_time as updated_time,w.reg_date as reg_date
FROM
multi_requests m JOIN unique w ON m.request_id = w.request_id
WHERE
m.request_time
Between
'2015-07-05'
AND
'2015-07-06'
GROUP BY
m.request_id
ORDER BY
m.id asc
LIMIT
0,10" ;
I am also trying to add an index to "request_id" in the multi_requests table. But when I add Index to the query "request_id" and search for the above query, it doesn't show any post types in the UI.
The table multi_requests has common records = 6033030. So please suggest me ..
source to share
This is your request:
SELECT m.id, m.request_id as id, count(m.mac_address) as count, m.reqtype,
m.request_time, w.status as status, w.updated_time as updated_time,
w.reg_date as reg_date
FROM multi_requests m JOIN
unique w
ON m.mac_address = w.mac_address
WHERE m.request_time Between '2015-07-05' AND '2015-07-06'
GROUP BY m.request_id
ORDER BY m.id asc
LIMIT 0, 10 ;
This is a bit weird because you have a ton of columns in select
, but only one in group by
. Let me assume that you know what you are doing.
For this query, the best indices are on multi_requests(request_time, mac_address, request_id)
and unique(mac_address)
.
source to share