how to optimize this query

[mga_ka_php]

how do you optimize this query?

SELECT SUBSTR(eventDetails, INSTR(eventDetails, '|#|Name') + 8, INSTR(eventDetails, '|#|StartDate') - INSTR(eventDetails, '|#|Name') -  eventTitle, SUBSTR(eventDetails, INSTR(eventDetails, '|#|City') + 8, INSTR(eventDetails, '|#|State') - INSTR(eventDetails, '|#|City') -  city, IF(UNIX_TIMESTAMP(SUBSTR(eventDetails, INSTR(eventDetails, '|#|StartDate') + 13, 10)) = 0, SUBSTR(eventDetails, INSTR(eventDetails, '|#|StartDate') + 13, 10), UNIX_TIMESTAMP(SUBSTR(eventDetails, INSTR(eventDetails, '|#|StartDate') + 13, 10))) eventStartDate, IF(UNIX_TIMESTAMP(SUBSTR(eventDetails, INSTR(eventDetails, '|#|EndDate') + 11, 10)) = 0, SUBSTR(eventDetails, INSTR(eventDetails, '|#|EndDate') + 11, 10), UNIX_TIMESTAMP(SUBSTR(eventDetails, INSTR(eventDetails, '|#|EndDate') + 11, 10))) eventEndDate, SUBSTR(eventDetails, INSTR(eventDetails, '|#|State') + 9, INSTR(eventDetails, '|#|Zip') - INSTR(eventDetails, '|#|State') - 9) eventState
FROM events 
WHERE eventDetails NOT LIKE '%Canceled:YES%' 
AND eventDetails NOT LIKE '%State:|#|%' 
AND eventDetails NOT LIKE '%Status:DELETED%' 
AND (eventDetails NOT LIKE '%PromoterPhone:|#|%' OR eventDetails NOT LIKE '%Link1:' OR eventDetails NOT LIKE '%UserNumber:0|#|%' OR eventDetails NOT LIKE '%UserNumber:|#|%')
AND SUBSTR(eventDetails, INSTR(eventDetails, '|#|State') + 9, INSTR(eventDetails, '|#|Zip') - INSTR(eventDetails, '|#|State') - 9) = 'RI' 
HAVING ((eventStartDate BETWEEN '1261980000' AND '1282712400') AND (eventEndDate BETWEEN '1261980000' AND '1282712400')) 
ORDER BY eventStartDate ASC LIMIT 0, 20;

[DavidAM]

Redesign the database. Cancelled, State, Status, and so forth should probably be individual columns in the table, not embedded in a text field.

 

As it stands you need to do two things:

1) Change HAVING to AND - HAVING is optimized for GROUP BYs and will not filter the rows being selected, it filters the rows being returned.  A subtle difference, but it basically means that it will not be considered until all other conditions are met.

 

2) Make sure that eventStartDate and eventEndDate are individual indexes on the table.  They do not have to be unique indexes.  LIKE will never use an index, so you have to limit where it is looking. By changing the HAVING and letting the engine use an index, you should get better performance. You are most likely doing table scans right now.

 

I don't think there is anything else that can be done since all the other conditions are using LIKE.