CODEWITHSUNDEEP
mysql
Carmen
Carmen

Reputation: 2873

MySQL Index + Query Processing

Assume I have this table:

create table table_a (
    id int,
    name varchar(25),
    address varchar(25),
    primary key (id)
) engine = innodb;

When I run this query:

select * from table_a where id >= 'x' and name = 'test';

How will MySQL process it? Will it pull all the id's first (assume 1000 rows) then apply the where clause name = 'test'?

Or while it looks for the ids, it is already applying the where clause at the same time?

Upvotes: 2

Views: 608

Answers (5)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

You can get information on how the query is processed by running EXPLAIN on the query.

If the idea is to optimize that query then you might want to add an index like:

alter table table_a add unique index name_id_idx (name, id);

Upvotes: 0

mykey
mykey

Reputation: 575

you can compare the execution times by executing the query first when the id comes first in the where clause and then interchange and bring the name first. to see an example of mysql performance with indexes check this out http://www.mysqlperformanceblog.com/2006/06/02/indexes-in-mysql/

Upvotes: 0

user1191247
user1191247

Reputation: 12973

As id is the PK (and no index on name) it will load all rows that satisfy the id based criterion into memory after which it will filter the resultset by the name criterion. Adding a composite index containing both fields would mean that it would only load the records that satisfy both criteria. Adding a separate single column index on the name field may not result in an index merge operation, in which case the index would have no effect.

Upvotes: 2

Ata S.
Ata S.

Reputation: 900

For the best result, you should have a single index which includes both of the columns id and name.

In your case, I can't answer the affect of the primary index to that query. That depends on DBMS's and versions. If you really don't want to put more index (because more index means slow write and updates) just populate your table with like 10.000.000 random results, try it and see the effect.

Upvotes: 0

scorpdaddy
scorpdaddy

Reputation: 303

Do you have indexes on either column? That may affect the execution plan. The other thing is one might cast the 'x'::int to ensure a numeric comparison instead of a string comparison.

Upvotes: 0

Related Questions