Re: Slow search on one table, many where, order by and limit

[Yair P]

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