CODEWITHSUNDEEP
mysqlsqloptimizationselectindexing
Matthias Munz
Matthias Munz

Reputation: 3765

SQL: How to optimize many SELECTs on single table

I have created a table with

CREATE TABLE xy (
blub VARCHAR(50) NOT NULL,
z INT UNSIGNED NOT NULL,
PRIMARY KEY (blub)
)

and want to query many entries at once by the column 'blub'. Currently I am using the simple query

SELECT blub, z FROM xy WHERE blub = '...'

which is much faster than 

SELECT blub, z FROM xy WHERE blub = '...' OR blub = '...' OR '...

Is there any other way to make it faster and to merge all queries?

Upvotes: 2

Views: 178

Answers (4)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

You need to add an index

CREATE TABLE xy ( 
blub VARCHAR(50) NOT NULL, 
z INT UNSIGNED NOT NULL, 
PRIMARY KEY (xy),
KEY (blub,z)
)

If your table is already populated then do this

ALTER TABLE xy ADD INDEX (blub,z);

then, SELECT blub, z FROM xy WHERE blub in ('a','b','c') will be faster

Upvotes: 1

Tim Pote
Tim Pote

Reputation: 28029

You can try an in clause. I'm not certain it will be faster, but it will definitely be more succinct. For example:

select blub, z from zy where blub in ('this', 'that', 'theother');

explain will tell you which is actually fastest for your situation.

Upvotes: 2

Taryn
Taryn

Reputation: 247690

Use an IN clause in your WHERE like this:

SELECT blub, z 
FROM xy 
WHERE blub IN ('value1', 'value2', 'value3')

As far as which one runs better/faster you can use the query execution plan for the query. In Sql-server you Click "Include Actual Execution Plan". In MySql, you could use the EXPLAIN function.

Upvotes: 1

Nesim Razon
Nesim Razon

Reputation: 9794

SELECT blub, z FROM xy WHERE blub in ('a','b','c')

Upvotes: 2

Related Questions