To answer your questions in no particular order, YES you can speed it up with indexing.
You might want to first create an index on ( blocksize AND physmessage_id ). Why, you might ask, index on physmessage_id? Because then the db won't have to do a fetch on items from the table since it's in the INDEX itself, saving any unnecessary reads. Realistically, I can't see that taking more than a few seconds, at most, to execute. However, making the index might take a serious bit of time. Please let us all know how it does or does not work. Tim... -----Original Message----- From: Ken D'Ambrosio [mailto:k...@jots.org] Sent: Wednesday, June 24, 2009 11:07 AM To: mysql@lists.mysql.com Subject: Indexing? (Warning: relative newbie.) Hi, all. I'm a long-time MySQL user who's only recently had to start learning some administrative stuff, largely because I finally have a decently-sized database. My database is about 100 GB; I'm using it -- via dbmail (www.dbmail.org) -- as a mail server for my company. While dbmail is well-and-good with its IMAP front-end, I'm thinking of writing a Python front-end to do some queries directly against MySQL. But some of them take a l-o-n-g time. As an example, I've got a table with slightly over a million records; I'd like to be able to show (say) only IDs of messages under a half-MB. The query would look something like this: select physmessage_id,blocksize from dbmail_messageblks where blocksize < 500000; That query takes 50 minutes. A smidge long to wait. So I said, "Huh. That's impressive." And I tried it without the physmessage_id: select blocksize from dbmail_messageblks where blocksize < 500000; That took 14 seconds. A bit more in my timeframe. Can I optimize this with indexing? Should I be using a different DB engine? Is there a site/book I should be learning DBA fundamentals from that might offer me direction for stuff like this? Sorry for all the newbie questions, but I haven't done serious database stuff since Foxbase/dBase III days. Things have changed a little since then. Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=tlit...@tgrnet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
