MySQL Forums
Forum List  »  Optimizer & Parser

Re: Slow search on one table, many where, order by and limit
Posted by: Rick James
Date: February 26, 2010 07:40PM

| key_buffer_size | 26214400 |
Index_length: 69526528

How much RAM? Assuming you have at least 1GB, recommend
key_buffer_size = 100M
so that all the indexes for that table have a chance of staying in RAM.

Are you really SELECTing CARD_ID? Or are you selecting a lot of other columns? If just CARD_ID, then I recommend
  INDEX(GENDER, COUNTRY_ID, LOOKING_FOR_GENDERS, CARD_FLAGS, CARD_ID)
That way, the SELECTs that you showed can be run "Using index". That tends to be somewhat faster than having to run off to the data for each row.

Consider adding "unknown" to the ENUMs and making them NOT NULL. I don't know how much this will help; perhaps not enough to be worth doing.

Some reason for height not being a TINYINT UNSIGNED?

A bunch of the INTs could be SMALLINT or TINYINT. (Ok, your table is only 41MB, so this won't really help any.)

Recommend you turn on the slowlog, and set long_query_time = 2, then we can discuss the naughties that show up in the slowlog.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow search on one table, many where, order by and limit
1774
February 26, 2010 07:40PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.