Missing Index?

[mrdiablo]

I think I found a missing index. This query is taking very long and is used to load a users profile page.

SELECT DISTINCT index_item_index_id FROM `core_search_index` WHERE ( index_item_index_id IN(66076,66076,1381,66076,66076,66076,66076,66076,84087,66076,66076,84087,3960,3960,84100,83341,81824,83988,83951,51662,83990,82887,82887,84029,82887) ) AND index_author=250

It takes about 15 seconds to get any data. So I added a test index to the core_search_index including index_item_index_id and index_author. Improved the speed of the query to about 0-0.2 seconds.

Is this index really missing? Or is it some other kind of problem?

[mrdiablo]

I still have problems that the profile page is extremly slow (takes 12 seconds of loading). I found a query that is taking long, but I have no idea why.

SELECT
main.*
FROM `core_search_index` AS `main`
WHERE
(
	(
		index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply')
	) OR index_class='IPS\\forums\\Topic\\Post'
)
AND (
	index_author=250 OR
	(
		index_class='IPS\\core\\Statuses\\Status' AND index_container_id=250
	)
)
AND (
	index_permissions = '*' OR
	(
		(
			FIND_IN_SET(4, index_permissions) OR FIND_IN_SET(13,index_permissions) OR FIND_IN_SET('m250',index_permissions)
		)
	)
) 
ORDER BY index_date_created
DESC LIMIT 0,15

The slow part seems to be the seconds one.

select count(*) from core_search_index where index_author=250 OR
(
index_class='IPS\\core\\Statuses\\Status' AND index_container_id=250
);

is very slow, but when I run them seperated both are fast.

select count(*) from core_search_index where index_author=250 ;
select count(*) from core_search_index where index_class='IPS\\core\\Statuses\\Status' AND index_container_id=250; 

Any idea how to fix that issue? I tried creating some more indexies but that didn't help :/