CODEWITHSUNDEEP
mysqlsql
David Diez
David Diez

Reputation: 679

SQL Query Speed When Table has Column of Type "Text"

I am designing an SQL database (accessed via PHP/MySQL) and have questions about designing the database in a way that helps the website run relatively quickly. My precise question pertains to speed when querying a table with many columns where one column is of type text. I am wondering, if

will the presence of the text column in the table affect the query speed? As a follow-up, do text columns generally slow down database queries?

Any other general tips on database design to help boost query speed are appreciated, as are any suggestions for books or other references on this topic. Thanks!

Upvotes: 1

Views: 5709

Answers (3)

Steven de Salas
Steven de Salas

Reputation: 21467

The 'text' data type will only slow your queries down if you intend to filter using this column in the WHERE clause of your select statements.

SELECT textColumn
FROM table WHERE varcharColumn LIKE '%Spanner%'

can be optimised more easily than

SELECT varcharColumn
FROM table WHERE textColumn LIKE '%Spanner%'

however 

SELECT textColumn
FROM table WHERE integerColumn = 1

performs just as well as

SELECT varcharColumn
FROM table WHERE integerColumn = 1

Some general tips:

As a general rule you should think about how your tables are going to be ordered in your output (by date or alphabetically?) and put an index on that column.

If you are starting out with DB design you should generally have all your tables using an INT Primary Key that is also your IDENTITY column AND Clustered Index. This means that the tables will be physically ordered (on disk) by that column (generally your ID such as PersonID etc), then use Non-clustered indexes on the columns that you are going to filter and order by.

At a later stage when you've built a few DBs I'd recommend you go further into optimising table design by setting your Clustered Index to be the unique column that is most frequently being used to order the table, including using multiple columns as your Clustered Index.

Upvotes: 3

amelvin
amelvin

Reputation: 9051

I'm not convinced that text columns are much slower than the alternatives.

Specifying the columns to be returned is a good performance choice - as there is no need to move more data than is needed across the wires.

If you get your indexes right you will get far better performance improvements than using text columns will cost.

If you are doing more many more database reads than writes then indexes will improve read speed.

Helping your optimizer by regularly dropping and re-adding indexes will also help as the data shape of your tables will change over time.

Upvotes: 2

andreas
andreas

Reputation: 1595

afaik there is no difference if you add a text column to a table, as long as you do not use it in the where clause. if you use it in the where clause it's definately good to have an index on it. Avoid comparsions with like, as they are slower.

Upvotes: 4

Related Questions