Re: Opinions on this schema please =)
Posted by:
Rick James
Date: February 21, 2009 12:07AM
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.
Subject
Written By
Posted
February 19, 2009 03:34PM
February 19, 2009 05:53PM
Re: Opinions on this schema please =)
February 21, 2009 12:07AM
February 21, 2009 07:59AM
February 21, 2009 10:47AM
February 21, 2009 05:40PM
February 22, 2009 01:36PM
February 22, 2009 02:13PM
February 22, 2009 08:41PM
February 23, 2009 09:13AM
February 23, 2009 09:36AM
February 23, 2009 04:27PM
February 26, 2009 05:15PM