Jonathan Feally wrote: > Paul, > You missed the DATE sort option. Got it.
> SORT has options for: > ARRIVAL (internal_date) > CC > DATE > FROM > SIZE > SUBJECT > TO > with the optional REVERSE tag in front to sort in opposite order. > All accounted for now. > I'm not sure it runs any faster with views than with the larger query. I don't know. I don't think it really matters, speed wise. > Since you are left joining the view, it does allow for non-existent > headers on messages to still be included in the sort result set. As required, right? > I have > been playing with a different approach, that still uses the uglier > query, but uses an incrementing int for the table short name in the > query. It will then allow any number of headers with any name to be > included in the sort/search. Only a handful of headers needs to be matched for sorting. > I see that for SEARCH we are doing a query > for each search criteria. Correct. > I think that all of the searching could be > done in a single query, but I'm not sure which way is better or faster. > It is a nice solution for making an ugly query nicer. Doing a single massive query is: - very complex since we need to support arbitrarily deeply nested search arguments. - quite likely slower than sequentially performing simpler queries whose result is joined client-side. > The goal of simplifying the schema seems to be getting lost in adding > the views. I thought the reason for doing so was reducing storage. Afaik views don't have a storage impact. > While it is better, it still relies on the schema to be > correct for everything to work. Non-sequitur. Everything depends on the schema to be correct. > I'd really like to figure out a > transition plan to allow the older messageblks table to be dropped. I > think we could make a new check function in the dbmail-util that would > go through all the phymessages in the old table and reinsert them into > the new mimeparts and update all of the messages to point to the new > phymessage_id. Once all the old messages are reinserted into the new > structure, then the table could be dropped. In general I do agree here. > The code to read from the > old table can then be toggled off if the table is absent. We could have > this functionality be there only until 2.4.0 is released. 2.4.1 and > beyond would no longer have any references to the messageblks table. > This leaves users a path to go from 2.2.x - to 2.4.0. They could then > upgrade 2.4.0 to the latest 2.4.x with any other schema changes since 2.4.0. But I'm not in favor of such a timepath. If you have a massive amount of data stored in the messageblks table, I wouldn't appreciate the forced upgrade path. Keeping the messageblks code in place in the 2.4 series, and dropping it in 2.5 seems much friendlier. We can start issuing deprecation warnings well ahead of time. > If I was a database administrator, the less schema, the better I would > think. I'm not sure that mysql is going to do much with the views as far > as caching/prefetching is concerned, so It may actually add processing > time to the queries instead of speeding them up. > Any DBA's out there want to chime in on using views vs. a query that > does it all in one? Performance, Best Practice, Pros/Cons?? -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ Dbmail-dev mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
