CODEWITHSUNDEEP
mysqlperformance
Matthew
Matthew

Reputation: 640

Does the no. of columns in where clause affect the speed of query in mysql

I have searched everywhere but did not find any answer in this,

For example I have 8 columns in my where clause like this sample query:

select col1,col2,col3,col4,col5,col6,col7,col8 where col1 = 1 and col2 = 1 and col3 = 1 and col4 = 'sample' and col5 like '%sample%' and col6 = 1 and col7 like '%sample%'and col8 = '1'

I have tested this in my pc and the speed is just fine, But I do not know if this query will be fast in other pc that has lower specs, because the pc i'm using have high specs. I do not have other pc to test this kind of query.

Also some of the columns in my table are not indexed because its not applicable.

Thank you in advance

Upvotes: 0

Views: 77

Answers (2)

Ed Heal
Ed Heal

Reputation: 59987

Of course it is bound to effect performance to some extent - you are asking the database to do more work.

To help the query out make sure you have an appropriate composite index. Put the ones that are equal are the start and the like ones (but do not bother with like '%dffd') at the end i.e.

CREATE INDEX ON <table name>
(col1, col2, col3, col4, col6, col8)

Upvotes: 1

Jeroen Ingelbrecht
Jeroen Ingelbrecht

Reputation: 808

There isnt a right or wrong number of columns to have in a table as the data that you are working with and the way you work with it should dictate your database design. (from: http://ask.sqlservercentral.com/questions/8461/will-number-of-columns-in-a-table-can-affect-perfo.html)

Some more performance related info regarding the where clause: http://use-the-index-luke.com/sql/where-clause

Upvotes: 1

Related Questions