| What is faster: Columns or Rows [message #496] |
Thu, 21 December 2006 04:32  |
 willybnl2
Messages: 6
Registered: December 2006 |
Junior Member |
|
|
Let's say I have a table that contains text (still varchar) in a lot of languages (let's say 50 languages).
I would only use this to do selects the whole day.
And i always need to search for 1 key in 2 or 3 of this languages with 1 query and display this to the user.
What would be faster:
ID english lang2 lang3
1 text text text
(so I get both long vertical/horizontal table)
OR
ID lang text
1 english text
(so I get very long vertical table)
Normalization tells me the last, but using MySQL 4 i noticed the top one is faster for as much i can test... but what should be faster (maybe I'm testing wrongly  )
[Updated on: Thu, 21 December 2006 04:43]
|
|
|
|
| Columns are faster then rows [message #500 is a reply to message #498 ] |
Fri, 22 December 2006 18:34  |
willybnl2
Messages: 6
Registered: December 2006 |
Junior Member |
|
|
Final results:
A horizontal table is WAY faster if it becomes very big.
Horzontal contains:
262,069 total rows
with id dutch english french german spanish italian romanian as columns
Vertical includes exactly the same information and thus becomes
1,568,702 total rows and only id, languageID, text
====
HORIZONTAL TABLE
* with ID primary (no index further)
SELECT SQL_NO_CACHE english, dutch, id
FROM phrases
WHERE (
`dutch` LIKE '%hoe%'
OR `english` LIKE '%hoe%'
)
AND (
`dutch` LIKE '%lang%'
OR `english` LIKE '%lang%'
)
AND (
`dutch` LIKE '%duurt%'
OR `english` LIKE '%duurt%'
)
LIMIT 0 , 30
==> 0.0081
== On very big database => 0.2149
--- 2nd search method
SELECT SQL_NO_CACHE dutch, english
FROM `phrases`
WHERE id
IN ( 50400011, 60100011, 60100027 )
==> 0.0004 sec
== On very big database => 0.0269
And after putting index on ID (for big table: 0.0013
=========================================================
VERTICAL TABLE
* With language and ID primary, no index further
SELECT SQL_NO_CACHE *
FROM site_phrases
WHERE
(language = 0 OR language = 1)
AND phrase LIKE '%hoe%'
AND phrase LIKE '%lang%'
AND phrase LIKE '%duurt%'
==> 0.0049
== ON very big database ==> 0.7106
--- 2nd search method
SELECT SQL_NO_CACHE *
FROM site_phrases
WHERE
(language = 0 OR language = 1)
AND
id IN ( 50400011, 60100011, 60100027 )
==> 0.0007
== ON very big database ==> 0.1460
After putting index on ID ==> 0.0005
And for big table with index ==> 0.0706
the results are very clear: NOT normalizing this table layout is WAY faster
[Updated on: Fri, 22 December 2006 18:43]
|
|
|
|
| Re: What is faster: Columns or Rows [message #505 is a reply to message #504 ] |
Sun, 24 December 2006 20:05 |
willybnl2
Messages: 6
Registered: December 2006 |
Junior Member |
|
|
I've tested that too:
==SPLITTED VERTICAL TABLE
SELECT SQL_NO_CACHE lang_Dutch.id,lang_Dutch.text, lang_English.text FROM lang_Dutch
JOIN lang_English using (ID) WHERE (
lang_Dutch.text LIKE '%hoe%'
OR lang_English.text LIKE '%hoe%'
) AND
(
lang_Dutch.text LIKE '%lang%'
OR lang_English.text LIKE '%lang%'
) AND
(
lang_Dutch.text LIKE '%duurt%'
OR lang_English.text LIKE '%duurt%'
)
LIMIT 0 , 30
= 1000rows, 6languages ==> 0.0196
--union method
SELECT SQL_NO_CACHE lang_Dutch.id, lang_Dutch.text, lang_English.text
FROM lang_Dutch
JOIN lang_English
USING ( ID )
WHERE (
lang_Dutch.text LIKE '%hoe%'
OR lang_English.text LIKE '%hoe%'
)
AND (
lang_Dutch.text LIKE '%lang%'
OR lang_English.text LIKE '%lang%'
)
AND (
lang_Dutch.text LIKE '%duurt%'
OR lang_English.text LIKE '%duurt%'
)
LIMIT 0 , 30
UNION
SELECT SQL_NO_CACHE lang_Dutch.id, lang_Dutch.text, lang_English.text
FROM lang_English
JOIN lang_Dutch
USING ( ID )
WHERE (
lang_Dutch.text LIKE '%hoe%'
OR lang_English.text LIKE '%hoe%'
)
AND (
lang_Dutch.text LIKE '%lang%'
OR lang_English.text LIKE '%lang%'
)
AND (
lang_Dutch.text LIKE '%duurt%'
OR lang_English.text LIKE '%duurt%'
)
LIMIT 0 , 30
== 0.0401 (to slow)
|
|
|
|
|
|
Members
Search
Help
Register
Login
Home
