Index by query UNION?
I have this union query:
(SELECT INSTALLER, INSTALLTIME, RESULT, JOBNUMBER, HONAME, ADDRESS, CITY, STATE, ZIP, NOTES, SMNOTES, '' as priority, PAFS, upsell, TERM, MMRUPGRADE, WARRANTY, EFT FROM ACCOUNTS
WHERE INSTALLDATE = '$date' && FUNDINGSTATUS !='DEAD')
UNION
(SELECT technician, servicetime, result, ID, Customername, address, city, state, zip, notes, board, priority, '', '', '', '', '', '' FROM service
WHERE serviceday = '$date')
ORDER BY INSTALLER, priority
I'm curious if an index on a date field would help speed up both queries? or will it be the fact that I am using FUNDINGSTATUS in the first one where the suggestion will make this query not use the index?
source to share
Answering your question:
I'm curious if an index on a date field would help speed up both queries?
If the condition is on installdate
and serviceday
is optional (it's multiple lines that satisfy it), then yes, that helps.
Date fields usually tend to be selective.
or will the fact that I use
FUNDINGSTATUS
the first one where the suggestion will make this query not use the index?
Yes, the index will still be used.
The engine will use the index to select only records with installdate = $date
, and additionally will filter the value FUNDINGSTATUS
.
For best results, create the following indexes:
ACCOUNTS (installdate, fundingstatus)
service (serviceday)
If DEAD
is a frequent value for FUNDINGSTATUS
, it might be better to rewrite this query as follows:
SELECT INSTALLER, INSTALLTIME, RESULT, JOBNUMBER, HONAME, ADDRESS, CITY, STATE, ZIP, NOTES, SMNOTES, '' as priority, PAFS, upsell, TERM, MMRUPGRADE, WARRANTY, EFT
FROM ACCOUNTS
WHERE INSTALLDATE = '$date' AND FUNDINGSTATUS < 'DEAD'
UNION ALL
SELECT INSTALLER, INSTALLTIME, RESULT, JOBNUMBER, HONAME, ADDRESS, CITY, STATE, ZIP, NOTES, SMNOTES, '' as priority, PAFS, upsell, TERM, MMRUPGRADE, WARRANTY, EFT
FROM ACCOUNTS
WHERE INSTALLDATE = '$date' AND FUNDINGSTATUS > 'DEAD'
UNION
SELECT technician, servicetime, result, ID, Customername, address, city, state, zip, notes, board, priority, '', '', '', '', '', ''
FROM service
WHERE serviceday = '$date'
ORDER BY
INSTALLER, priority
so that you can use range access in both fields (installdate, fundingstatus)
.
source to share
Having an index on any field in a where clause can always improve performance, be it a lot or a little.
To answer the question of whether the index "date" will be used despite the first query having the value "FUNDINGSTATUS" in the where clause has 2 answers:
-
If there are no OTHER indexes on the table, then a specific date index will be determined. This is because searching for records with a specific date in an index works much less for the DB than searching for an entire table, even if it needs to check FUNDINGSTATUS after finding the specified records.
-
If there are other indexes on the same table, then the answer is "it depends".
It mainly depends on what% of the data will represent data that would not be quoted against the% of data for a certain date.
The optimizer usually tries to pick an index that will pick the fewest columns right away - for example. if your table has 100 days of data and 1/2 of them are dead rows then the date index will be chosen because it gives you 1% of the data without a table scan versus 50% of the data.
source to share