-2

I have 2 tables with the same column (table A for a kind of product, table B for another type..) with 10k of rows per tables

I have 2 questions:

q1) How can I have the best performance in a SELECT query? with SELECT UNION ... or it's better to have a single table, with another column (type) and write a query like: SELECT ... WHERE... AND type='A' ?

q2) in the table I have a column named "object", and the medium lenght of the content in the rows is 160 character How can I have the best performance in a SELECT query? With a columntype VARCHAR (index) or with a TEXT ?

Thank You for suggestion and sorry for my english !

quattrocorde
  • 17
  • 6
  • For the first question, the second option of having a column that you could use with `WHERE` is probably the way to go. For the second question, varchar or text may not be the biggest issue as whether or not you have an index on those (and maybe other) columns. And `TEXT` has to have a prefix length to have an index. – Tim Biegeleisen Aug 24 '17 at 13:51

1 Answers1

0

As regards question 1, I don't know how you arrived to this design of schema, but, regardless of performance issues introduced by UNION, if two products have same attributes and only different type, I would use a unique table for both, specifying a TYPE column. If the columns differ considerably, maintain your two table architecture (to avoid having a single table with a lot of null entries), and read about UNION performance here The effects UNION in a SQL query.

As regards question 2, refer to this thread MySQL: Large VARCHAR vs. TEXT? . In your case VARCHAR is perfectly fine for inline storage of the text of that column.

Hope to be helpful.

Nicola Pellicanò
  • 469
  • 2
  • 5