Paul, You missed the DATE sort option. 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.
I'm not sure it runs any faster with views than with the larger query. 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. 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. I see that for SEARCH we are doing a query for each search criteria. 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. The goal of simplifying the schema seems to be getting lost in adding the views. While it is better, it still relies on the schema to be correct for everything to work. 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. 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. 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?? -Jon Paul J Stevens wrote: > Jon, > > I decided to try a different approach here. I just pushed out a change > that uses views to mimic the old tables (dbmail_subjectfield, > dbmail_tofield, dbmail_ccfield, dbmail_fromfield) that were used in > imap-sort. That allows me to stick to the join/order approach used in > dbmail-2.2 while leveraging the new single-instance header tables. > > Let's see how this rolls. > > > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. _______________________________________________ Dbmail-dev mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
