I’m hosting MySQL Indexes: Best Practices Webinar next week, Wednesday August 15. In which I will talk about how MySQL indexes are structured and how they can be used, as well as go over the best practices in selecting correct indexes for your database workloads.
Indexing looks like a very basic topic, but it is rather deep one. Most people would think they know how to index their database properly but this just applies to basics, and more often than not a lot of opportunities for optimizations are left on the table. You can often get 3x better performance by having your database well indexed, compared to “basically” indexed.
In addition to talking about indexes themselves I will also talk about some query tricks you can use to get MySQL to use indexes when it could not before. This is another tool every MySQL DBA should have in his toolbox.
As usually there will be time for questions so do not forget to bring yours. Register now to attend.
Will it be possible to talk about indexing when you have “advanced” forms: multiple (long) form fields with some that are optional. Exemple: car search (mileage minimum, mileage maximum, year minimum, year maximum, has picture, keywords (full text search), etc.).
Thanks.
Idont, I recommend using some other database than mysql for that kind of search, my first choice would be Sphinx.
I’d love some way of informing (and constraining) the database that a time column is monotonic, as many log files are.
For now what I do is an auto-increment primary key and an indexed datetime column, and use two queries:
The first translates a date range to a primary key range:
mnm, mxm = db.do1(“SELECT MIN(pkey), MAX(pkey) FROM tbl ”
“WHERE dt_field BETWEEN %s AND %s”,
(min_date, max_date))
The second uses two BETWEEN clauses to benefit from the fact that the optimizer “knows” pkey ranges and insure that a slightly misplaced entry (time jitter on web servers) will still be properly classified.
for row in db.doall(“SELECT user_id, SUM(charges) FROM tbl ”
“WHERE dt_field BETWEEN %s AND %s ”
“AND pkey BETWEEN %s AND %s ”
“GROUP BY user_id”,
(min_date, max_date, mnm, mxm)):
print (‘{0:3d}: {1:9.2f}’.format(*row))