Re: Opinions on this schema please =)

[Rick James]

The schema might be too normalized.

The SELECT you gave as an example is a nightmare!
The construct "FROM ( SELECT ... )" is sometimes useful, but is usually very inefficient -- see if you can do the equivalent with JOIN. (Note: The temp table inside (...) will have no indexes.)

OR, especially between fields in different tables, will punt on optimization. Turn it into UNION.

If you are going to search on key-value pairs, it is terribly inefficient to use the generic method you are using. Having an indexed column for "make" is much more efficient. However, I understand that you may need more generality than that will allow. See what kind of compromise you can achieve.

I'm looking at a web site build around k-v schema. It has a 7-way join for the SELECT. The slowlog is full of SELECTs taking, typically, 12 seconds to run. If those 7 keys were turned into 7 columns (with some index), the query would run in much less than a second. The data has dozens of keys, but only those 7 are ever used for selecting articles.

If your data grows too big to fit in RAM, performance will take another nosedive -- you will be thrashing the disk as your queries do table scans and create temp tables just to find a few rows.

You're a "performance freak"? So am I, plus a "scalability freak". Some people call me SuperFreak, that that's another matter.