[TYPO3-Performance] mysql performance

Dmitry Dulepov [typo3] dmitry at typo3.org
Fri Jul 11 15:54:37 CEST 2008


Hi!

Christoph Mayer wrote:
> My website has altogether 20 possible $_GET parameters and I want to log all
> $_GET params of each click in a table. Every click has just a few an not all
> $_GET params. So I think I have two possibilities for the table design:
> 
> 1. table with 20 fields for all parameters 
> or
> 2. table with field for url_id and 2 extra fields for key and value

<skip>

> So which table design is better in order to get a performante table?
> - table design 1 has only one entry to each click but a lot of empty fields
> - table design 2 has several entries to each click but no empty fields

No one will be able to give you right answer. Not because no one knows but because speed will depend on many factors including amount of data in the table and how you use this data later.

For inserting the first thing will probably faster because it inserts only one row.

For querying it will depend on the query. There can be several cases here:
- get all parameters for URL: the first design will win in 99.9% cases I think (locate a single row by index and fetch it)
- get value of a parameter for the given URL. This will depend on your indexes but results can be comparable for both design. Slightly more chances for better performance for the first design because you will not need a composite index (which is prone to be ignored if there is too much data).

If you do not use InnoDB, the first case always wins if you have to read and write to the table. MyISAM always locks tables with exclusive locks when writes happen. So if insert 20 rows, it will take longer for anyone who wants to read the table at the same time. If you use InnoDB, read speed will not be affected.

You must also consider amount of rows. In the first case you will have less of them, so MySQL may choose to use index for most of the time. In the second case it may decide that full table scan is the same time or faster than using index and you get worse performance. EXPLAIN will not help you much because it makes estimates using current data in the table. Unless you have real data, EXPLAIN is useless. And when you have real data, it is too late to change table structure...

So, if I were you, I would go for the first design. I would declare all value fields as NULLable and see what index type I should use for url_id.

-- 
Dmitry Dulepov
TYPO3 Core team
More about TYPO3: http://typo3bloke.net/
Subscribe: http://typo3bloke.net/rss.xml


More information about the TYPO3-Performance mailing list