CODEWITHSUNDEEP
mysqlsqloptimizationinnodb
mahemoff
mahemoff

Reputation: 46419

MySQL Optimization When Not All Columns Are Indexed

Say I have a table with 3 columns and thousands of records like this:

id # primary key
name # indexed
gender # not indexed

And I want to find "All males named Alex", i.e., a specific name and specific gender.

Is the naieve way (select * from people where name='alex' and gender=2) good enough here? Or is there a more optimal way, like a sub-query on name?

Upvotes: 2

Views: 96

Answers (3)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

Assuming that you don't have thousand of records, matching the name, with only few being actually males, the index on name is enough. Generally you should not index fields with little carinality (only 2 possible values means that you are going to match 50% of the rows, which does not justify using an index).

The only usefull exception I can think of, is if you are selecting name and gender only, and if you put both of them in the index, you can perform an index-covered query, which is faster than selecting rows by index and then retrieving the data from the table.

Upvotes: 1

Fluffeh
Fluffeh

Reputation: 33512

If creating an index is not an option, or you have a large volume of data in the table (or even if there is an index, but you still want to quicken the pace) it can often have a big impact to reorder the table according to the data you are grouping together.

I have a query at work for getting KPIs together for my division and even though everything was nicely indexed, the data that was being pulled was still searching through a couple of gigs of table. This means a LOT of disc accessing while the query aggregates all the correct rows together. I reordered the table using alter table tableName order by column1, column2; and the query went from taking around 15 seconds to returning data in under 3. So the physical gathering of the data can be a significant influence - even if the tables are indexed and the DB knows exactly where to get it. Arranging the data so it is easier for the database to get to everything it needs will improve performance.

Upvotes: 1

Ed Heal
Ed Heal

Reputation: 60007

A better way is to have a composite index.

i.e.

CREATE INDEX <some name for the index> ON <table name> (name, gender)

Then the WHERE clause can use it for both the name and the gender.

Upvotes: 0

Related Questions