Yesterday I was working on Records field. As a result of new architecture it had to be totally rewritten and I did not take even a line of the code from old field, only XML parameters which was refactored too.
At first I was very excited because field is excellent. It is not only show different type of data it also order it accordingly. For example you show “COLOR” field related records. Current record has 2 colors red and blue. So it will show all records which have red or blue but records which have both colors will be first. Or tag related also will show all records that have the same tags but more tags match, the higher will be the article. Another words there is very nice algorithm that will show users most possible needed records.
But suddenly I felt like pages started loading slower. I checked in debug and bum! 2.5 seconds per page. For a second or two I felt completely desperate. What!? I though. It is one record list and speed goes down from 0.2 to 2.5?!! Are we going to get where we are now?!! I thought.
Then I calmed myself down and started analyze all sql queries of the page. There was about 47 queris on the page. And around 27 of them not of Black Ice or very simple like
SHOW FULL COLUMNS FROM `jos_js_res_types`
I started to copy every query produced by Black Ice to query analyzer and put through EXPLAIN. There was only one query that caused slowness but during this process I corrected many other queries too. Added correct indexes, … and here it is. 0.25 seconds came back.
And note it is all multiple type support. On attached image you can see that list of records display different content type “Electronics” and “Electronics 2” which have both the same “COLOR” field. And their “PRICE” field is in the same column although it is different field ID but same Label and field type.