Using MYSQL Views

[jciconsult]

I want to use a mysql view, by definition, this does not necessarily have a primary key because it is created by joining a number of tables. I am able to create a list but when I try to view data, it looks for a column ID which does NOT exist.
How do I get around that?
I have attached a zip of the sql definition of the view.

 

[troester]

You can define an other column (with unique values) as primary key in your list settings (autoincrement=no).
Set "Alter existing fields" =no so fabrik won't try to modify any (view) column, set list access levels add,edit=nobody.

 

[jciconsult]

Progress Made

I created a fake key in the view that would have unique values (from a combination of fields from two tables). If I browse the view in phpmyadmin, it takes 1.8 seconds.
However, view data in Fabrik is VERY slow.
I get timeouts processing the site menus.
I have included 2 jpgs, one for the error message (which actually shows the data), one for the structure of the virtual table as shown in phpmyadmin. I have also included the revized sql for the view.
I had added even more indexes to the tables in the query and improved the performance.
It is still slower than in phpmyadmin

 

[rob]

hard to tell just from those two screen shots what the issue might be.

Id run an explain on the initial query you use to build the view. Perhaps indexes could be added to the base tables.

I'm not sure that varbinary for a key is a wise choice - id set that to char(50) or similar. Already 1.8 seconds for mySQL to run a query is pretty slow.

I'd enable Joomla debug and look at the profiling tab to see where the loading time is.

I'd double check that the list has ItemKey set as the primary key

 

[jciconsult]

I actually got it to work by adding indexes. It is now down to .4 seconds and works adequately.
ItemKey is primary.
I will try an explain.