[drupal-devel] MySQL, large keys
David Axmark, co-founder of MySQL had a quick look at Drupal's database scheme and hinted that we should take a look at the length of our table keys. He suggested that reducing their size, might improve performance. Someone to investigate this, and to run some benchmarks?
-- Dries Buytaert :: http://www.buytaert.net/
Dries Buytaert wrote:
David Axmark, co-founder of MySQL had a quick look at Drupal's database scheme and hinted that we should take a look at the length of our table keys. He suggested that reducing their size, might improve performance. Someone to investigate this, and to run some benchmarks?
I might find time to do some. David also told us that the mysql query cache for a table is emtied when a table changes. That means thet the mysql query cache for the cache table is invalidated very often. Splitting up the cache table into dedicated tables for menu, filter, page, locale, default will therefore make the mysql query cache more efficient. Also, generally selects will be faster.
I have been creating a patch that I know is buggy and incomplete, but I let you all have a look nevertheless. I hope to be able to create some benchmarks shortly. Cheers, Gerhard
David Axmark, co-founder of MySQL had a quick look at Drupal's database scheme and hinted that we should take a look at the length of our table keys. He suggested that reducing their size, might improve performance. Someone to investigate this, and to run some benchmarks?
yes, we've been very lazy about this
I might find time to do some. David also told us that the mysql query cache for a table is emtied when a table changes. That means thet the mysql query cache for the cache table is invalidated very often. Splitting up the cache table into dedicated tables for menu, filter, page, locale, default will therefore make the mysql query cache more efficient. Also, generally selects will be faster.
you are optimizing selects that are already very very fast. these are single table queries with a single condition on an indexed column. i think that further optimizing drupal for the mysql query cache is a more involved effort. you have to simplify queries in order to avoid DISTINCT(), COUNT(), SUM(), etc. that often means performing subqueries or multiple successive queries. with mysql query cache enabled, i am seeing pages using 50ms of total query time. not much to improve upon IMO.
On 10/21/05, Moshe Weitzman <weitzman@tejasa.com> wrote:
you are optimizing selects that are already very very fast. these are single table queries with a single condition on an indexed column.
i think that further optimizing drupal for the mysql query cache is a more involved effort. you have to simplify queries in order to avoid DISTINCT(), COUNT(), SUM(), etc. that often means performing subqueries or multiple successive queries.
with mysql query cache enabled, i am seeing pages using 50ms of total query time. not much to improve upon IMO.
I think the point here is that the fast (50ms) queries occur on a cache hit. Splitting the cache table would increase the probability of cache hits as the entire cached data set won't be invalidated for every cache change. -- Best regards, Herman Webley
I think the point here is that the fast (50ms) queries occur on a cache hit. Splitting the cache table would increase the probability of cache hits as the entire cached data set won't be invalidated for every cache change.
sorry, i was unclear. i am seeing that the sum of all queries on the page is about 50ms.
participants (4)
-
Dries Buytaert -
Gerhard Killesreiter -
Herman Webley -
Moshe Weitzman