MYSQL speed of this query and optimized table structure

Question

I need to build a new table system, it'll store an id and 10 varchars(255) per id. That is all it will need to store. Other than the obvious insert/delete/update on whole rows only, the only other query that will be run is a SELECT * FROM table WHERE id='id'. 7 million records.

I have 2 structures I came up with, which are:

(1) - single table, id,then 10 varchars, no joins, nothing fancy, id is primary key, simple select *.

(2) - 2 tables, first has id, then 10 integer elements, second has integer(auto increment) and the varchars. This would use a join. Hence, I would guess 10 joins per query.

Clearly 2 is better as a formal structure and for later table structural changes, BUT, in terms of SPEED of querying alone, which is better?

Quassnoi · Accepted Answer · 2010-12-27 13:02:53Z

1

If you will always be storing exactly 10 VARCHAR fields and each VARCHAR has its own meaning (like it's always a name, or always an address etc.), then just create a table with 10 fields.

Your second solution is called EAV (entity-attribute-value), which is mostly used for sparse matrices (when you have lots of possible attributes with only few of them being set for a given entity). It is scalable and maintainable, but will be less efficient for the query like yours.

answered Dec 27, 2010 at 13:02

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Quassnoi Over a year ago

@David: first one (with all values in a single row). "Less efficient" in the EAV description means "slow" :)

Yasser Souri · Accepted Answer · 2010-12-27 13:05:22Z

0

definitely in terms of speed the first one is better. Joins are really slow on big tables

answered Dec 27, 2010 at 13:05

Yasser Souri

1,9552 gold badges19 silver badges26 bronze badges

2 Comments

David19801 Over a year ago

Can I get confirmation on "joins are really slow on big tables" please. I index the id column on table1 and integer column on table2

Yasser Souri Over a year ago

if you imagine what happens in joins its clear why they are slow in big tables.

Collectives™ on Stack Overflow

MYSQL speed of this query and optimized table structure

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related