Search slow yet advanced search fast

[Mustafa_s]

Hello,

Strange issue on my Fabrik app where when I search a list through the built-in list search it takes about 30 seconds to 1 minute. However, the advanced search (pop-up with options) takes less than 3 seconds, no matter what the search query is.

I have only 3 elements selected to be included in the search queries. 1 field, 1 drop down, 1 CDD. About 800 records or less. There are probably 15 elements in total from said list.

What are some debugging steps I can take to start troubleshooting?

Latest Joomla running latest Fabrik.

 

[cheesegrits]

Remember to post in Pro when you have a Pro membership, otherwise you may not get your questions answered!

Moving it this thread to Pro ...

-- hugh

 

[cheesegrits]

Is this your DDP site? If so, I'm getting the "your location (US) has been blocked" thing again.

In general, the best way is debug the query, so add &fabrikdebug=1, look at the getData debug output, copy that query, then use phpMyAdmin (or whatever client you use) to "explain" it, which will tell you everything you need to know about how the query is being indexed.

However, with such few rows, I suspect it's more likely to be something unrelated to the query itself.

-- hugh

 

[Mustafa_s]

I turned on debugging, and searched for a single word. I then took the getData output and described/explained it in mysql, it displayed about 40 seconds in total for Send Data, I don't think that's normal behaviour.

The query is as follows:

SELECT SQL_CALC_FOUND_ROWS DISTINCT `premfacesall`.`id` AS `premfacesall___id`, `premfacesall`.`id` AS `premfacesall___id_raw`, `premfacesall`.`file` AS `premfacesall___file`, `premfacesall`.`file` AS `premfacesall___file_raw`, `premfacesall`.`name` AS `premfacesall___name`, `premfacesall`.`name` AS `premfacesall___name_raw`, `premfacesall`.`team` AS `premfacesall___team_raw`, `premfaces_teams`.`teams` AS `premfacesall___team`, (SELECT GROUP_CONCAT(lookup.emotions SEPARATOR '//..*..//') FROM premfacesall_repeat_emotions LEFT JOIN premfaces_emotions AS lookup ON lookup.emotions = premfacesall_repeat_emotions.emotions WHERE premfacesall_repeat_emotions.parent_id = `premfacesall`.`id`) AS premfacesall___emotions, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM premfacesall_repeat_emotions WHERE parent_id = `premfacesall`.`id`) AS `premfacesall___emotions_raw`, (SELECT GROUP_CONCAT(emotions SEPARATOR '//..*..//') FROM premfacesall_repeat_emotions WHERE premfacesall_repeat_emotions.parent_id = `premfacesall`.`id`) AS premfacesall___emotions_id, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM premfacesall_repeat_emotions WHERE parent_id = `premfacesall`.`id`) AS `premfacesall___emotions___params`, `premfacesall`.`thumbs` AS `premfacesall___thumbs`, `premfacesall`.`thumbs` AS `premfacesall___thumbs_raw`, `premfacesall`.`getfilenameonly` AS `premfacesall___getfilenameonly`, `premfacesall`.`getfilenameonly` AS `premfacesall___getfilenameonly_raw`, `premfacesall`.`getinternalid` AS `premfacesall___getinternalid`, `premfacesall`.`getinternalid` AS `premfacesall___getinternalid_raw`, `premfacesall`.`copydetails` AS `premfacesall___copydetails`, `premfacesall`.`copydetails` AS `premfacesall___copydetails_raw`, `premfacesall`.`favorites` AS `premfacesall___favorites`, `premfacesall`.`favorites` AS `premfacesall___favorites_raw`, `premfacesall`.`owner` AS `premfacesall___owner`, `premfacesall`.`owner` AS `premfacesall___owner_raw`, `premfacesall`.`user` AS `premfacesall___user`, `premfacesall`.`user` AS `premfacesall___user_raw`, CONCAT_WS(':', `premfacesall`.`id`, `premfacesall`.`name`) AS slug , `premfacesall`.`id` AS `__pk_val` FROM `premfacesall` LEFT JOIN `premfaces_teams` AS `premfaces_teams` ON `premfaces_teams`.`teams` = `premfacesall`.`team` WHERE ( ( LOWER(`premfacesall`.`name`) REGEXP LOWER('david') OR LOWER(`premfaces_teams`.`teams`) regexp LOWER('david') OR LOWER((SELECT GROUP_CONCAT(lookup.emotions SEPARATOR '//..*..//') FROM premfacesall_repeat_emotions LEFT JOIN premfaces_emotions AS lookup ON lookup.emotions = premfacesall_repeat_emotions.emotions WHERE premfacesall_repeat_emotions.parent_id = `premfacesall`.`id`)) regexp LOWER('david')) ) ORDER BY `premfacesall`.`id` DESC

There are probably 5-6 rows in the result pane, though a lot of the columns have NULL if that's worth noting.

The site is premfaces, added to my sites.

 

[Mustafa_s]

Bump

 

[troester]

What are your CDD settings, is it using id[recommended] as value? If not check directly in the database if the column used as value has an index set.

 

[Mustafa_s]

Bingo.

Missing index on CDD "teams" table.

Thanks troester.