MySQL Forums
Forum List  »  Optimizer & Parser

Re: Slow search on one table, many where, order by and limit
Posted by: Yair P
Date: March 01, 2010 03:33AM

Hi Rick,

Thanks for your patience.

>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.

I have 2GB. Tried setting key_buffer_size to 100M from 25M but no noticeable improvement.



>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.

I select just CARD_IDs based on the search criteria.
I have quick search like:

SELECT CARD_ID
FROM cards
WHERE (LOOKING_FOR_GENDERS +0 &2) >0
AND GENDER IN ('MAN')
AND COUNTRY_ID =254
AND (CARD_FLAGS +0 &18) >0
ORDER BY RAND( )
LIMIT 0 , 320


Or Advanced search like:

SELECT CARD_ID
FROM cards
WHERE GENDER IN ('WOMAN', 'COUPLE')
AND (LOOKING_FOR_GENDERS +0 &1) >0
AND BIRTHDATE_YEAR <=1990
AND BIRTHDATE_YEAR >=1980
AND COUNTRY_ID =254
AND STATE_ID =126
AND CITY_ID =7275
AND (RELATIONSHIPS +0 &164) >0
AND MARITAL IN ('SINGLE', 'DIVORCED')
AND ETHNICITY IN ('CAUCASIAN', 'HISPANIC')
AND (LANGUAGES +0 &3) >0
AND (CARD_FLAGS +0 &2) >0
ORDER BY LAST_VISITED_DATE DESC
LIMIT 0 , 320

Note: these are the full queries but some search parameters may be missing if the user didn't select them.
The advanced query runs pretty fast (<100ms) after implementing your suggested indexing scheme.




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

c:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt, Version: 5.0.67-community-nt-log (MySQL Community Edition (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
c:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt, Version: 5.0.67-community-nt-log (MySQL Community Edition (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 100301 11:15:37
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 59 Lock_time: 0 Rows_sent: 320 Rows_examined: 403385
use susa;
SELECT CARD_ID FROM cards
WHERE (LOOKING_FOR_GENDERS +0 &2) >0 AND
GENDER IN ('MAN') AND
COUNTRY_ID =254 AND
(CARD_FLAGS +0 &18) >0
ORDER BY RAND( )
LIMIT 0 , 320;

[[[ Initial - took 59 seconds ]]]


# Time: 100301 11:16:22
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 4 Lock_time: 0 Rows_sent: 320 Rows_examined: 403385
SELECT CARD_ID FROM cards
WHERE (LOOKING_FOR_GENDERS +0 &2) >0 AND
GENDER IN ('MAN') AND
COUNTRY_ID =254 AND
(CARD_FLAGS +0 &18) >0
ORDER BY RAND( )
LIMIT 0 , 320;

[[[ after refresh - took 3.5 sec ]]]




c:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt, Version: 5.0.67-community-nt-log (MySQL Community Edition (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 100301 11:19:18
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 4 Lock_time: 0 Rows_sent: 320 Rows_examined: 403385
use sexpalusa;
SELECT CARD_ID FROM cards
WHERE (LOOKING_FOR_GENDERS +0 &2) >0 AND
GENDER IN ('MAN') AND
COUNTRY_ID =254 AND
(CARD_FLAGS +0 &18) >0
ORDER BY RAND( )
LIMIT 0 , 320;

[[[ after setting key_buffer_size = 100M - took 3.6 sec ]]]

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow search on one table, many where, order by and limit
2893
March 01, 2010 03:33AM


Sorry, you can't reply to this topic. It has been closed.

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.