MYSQL :: Slow Queries And Optimization

Slow Queries
I had a working web page that queried 20 tables and returned the data in the form of a table ... all of a sudden this stopped returning the results , and on investigation, up to ten tables, the query time is about 0.01 second ... but as the number of tables is increased to fifteen, the query time increases to 60 second .. and then gets too slow with nothing being returned at all.

A few days ago, this was all working fine. There have been no changes to the code on the web page ....
The version of mysql is 4.0.20, the server is a dual 1 MHz Xeon with 512 meg of RAM, running linux.

The tables have up to 15 fields each, and there is only simple text or numerical values in the fields.

Can anyone suggest what might have changed to suddenly slow down the query ?

What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?

Slow Queries
I just got a new dual opteron system, with raid 01, 2gb ram, and fedora
core linux running 2.4.22-smp kernel. For some reason mysql is running
pathetically slow.

Queries that should take 2ms occasionally take up to
20 seconds. It isn't every time, but almost once per page. The problem
usually occurs with queries accessing the large tables (up to 1gb), but
not always. I've tried 2 versions I compiled myself (with flags
suggested in the readme), as well as the version off the mysql website.
All were 4.0.18, and all had the problem.

Slow Queries
I have set long query time to 2 seconds in my cnf file. Therefore all the queries taking more than 2 seconds are logged in the file called mysql-slow.log and the file size is 20 Mb.
Is there anyway to sort this file and find out the queries those are taking the most time so that I can optimize only those ones

Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form.
My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot?
Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?

Enabling -- Log - Slow - Queries
I want to enable --log-slow-queries of MySQL.

How do I do it without restarting the DB server?

Slow View Queries
I have a fairly complex view which, when you execute a SELECT * shows all info very quickly (0.24 seconds to return nearly 3000 rows). However, when I start throwing slightly more complex queries at it, things really slow down - an average query can take 40 seconds to execute.

MySQL Timing Out? Really Slow Queries, Already Indexed.
For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got:

Uptime: 220372
Threads: 39
Questions: 18748899
Slow queries: 808
Opens: 28723
Flush tables: 1
Open tables: 4096
Queries per second avg: 85.078

The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling.

Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096:
query_cache_min_res_unit = 4096
table_cache = 4096
transaction_prealloc_size = 4096

Do any of those have anything to do with this? Am I even close? Are any of those values from mysql_stat a big deal?

Optimizing Slow Queries On Millions Of Records
I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and then
displays the results on the webpage.

When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
Now it takes about 38 seconds for all queries in the example below.
MySQL 4.1 on Windows

I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time. Code:

Slow Queries, 4 Million Records, Need Educated Advice!
I have created an app a few years back to store some records in a DB.
According our calculations we were never to exceed 500,000 records in
the DB. Seems we were off by a decimal point or so.

I set up a FreeBSD box with MySQL three years back and it has been
filling up. One table has over 4,000,000 records. Yes, four million.

As a web developer and not a DBA, I have struggled to upkeep the server
the best I can. As of the last one million records the server has been
struggling to keep up with multiple requests and as you can imagine the
user base is growing too.

Hardware:
Dual Xeon 3.06 Ghz
4 GB ECC RAM
800GB RAID5 SATA array

Software:
FreeBSD 5.3
Apache 2.0
PHP5
MySQL 5.0.2

Basically I have to perform a search on one of two columns in this huge
table (10 columns, 4 million rows).

The table is MyISAM with a single primary key that is used largely for
updating row data.

Most records are ten digit numbers for one column and a ten digit
varchar for the other, but sometimes either column can be a series of
characters up to 100 chars long, so each column is set for
varchar(100).

The action performed is

SELECT count(*) FROM tableName WHERE col LIKE "%123%"

then...

SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25

with "%123%" being any random string typed into a search window.

Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc.

I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables.

My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page.

Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?

Many Smaller Queries Vs. Large Comlicated Queries
I am wondering if any one can help me. I have a page that will run around 85 smaller queries but if i combine the queries it will go down by almost half. This page is a high traffic page and I don't a complicated query taking up mysql resources while it created a temp table and such. My question is this:

Is it better for mysql to run a lot of smaller queries (ex: simple selects with zero or one join, group by) or one larger complicated query with everything combined.

The thing i have to keep in mind is that the mysql selects are comming off the localhost that the web server is also running on so they share the same resources.

Optimization
I'm running MySQL 5.0.19 (windows), and running mostly well, but in
some cases seems too slow. I've been optimizing SQL as much as
possible, indexing important fields, using OPTIMIZE TABLE to clear out
deleted records, etc. I would also like to adjust the my.ini settings
to make sure things are set there to be as optimal as possible.

My
question is, when I look at the statistics in SHOW STATUS, is there any
guide for what to look for as far as what's running optimally or needs
improvement? I'm connecting to MySQL from ColdFusion.

Optimization
I have 2 tables that I join about (150,000 rows each)

in my where clause I have a statement that says ... t2.franchise!=3

now sometimes when the query is generated, there is a statements t1.feed=4

Now I know that when there is a feed 4, the franchise will never be 3

So, does taking out that extra franchise statement when I know it does not matter speed things up? I have about enough of these type of things that if would case me 3 or 4 conditionals in every query. I can just take care of them in php, but I will not waste my type if it will not matter.

Optimization
I wanted to ask information to you on like optimizing MySQL, in how much in the event that I illustrate I have found to you of 'the anomalous' performances.

In the first place I have intalled MySQL 5.0.11. I have a table 'Anag' with 31 fields, primary key Id AutoInc and one key univoque for Desc+Indirizzo+Localita.

If from the Query Browser I execute 'select cod, descr from anag' come extracted 150000 records in 4 second ones. If instead I execute 'select cod, descr from anag order by descr' the 150000 records they come extracted in 100 second ones.

I have tried to create a single index on the field descr, but the times do not change. The times are not change to you after to have made the 'Check Tables' and 'Optimize Tables'.

Not creed that is normal school that the clause Order by on an indexed field me must cost therefore a lot. What I could modify in order to improve the performances?

Optimization
I have a MySQl database on the backend of an Access front end where multiple users update client information as calls come into the company.  All of the data is now linked into Access from linked tables.

The problem is the response time when they are adding records.  It's not a heavy volume at all but there are about 10 users updating at the same time.  All users are set up under one username thread, as this was an easier way to set up the ODBC connection. (Is that the right way to go?)

My question is...what are the optimization parameters that I should be adjusting?  I am sure it has to do with the cache and the amount of memory being met for disk writes.  (Is this correct?)

Optimization
I need to get the last entry of a table, so I'm using the following query:

SELECT * FROM table WHERE (some conditions) ORDER BY date DESC LIMIT 0,1

This seems a little bit stupid... Does anybody know an another way to do that? Can I keep a ordered table and than avoi the use of "order by"? If yes, how can I do that?

Optimization
I read at MysQl.com about Explain SELECT, it says at then end of a =
paragraph to optimize your query, using filesort or temporary, but I =
went to MySQL search engine no luck on finding them, can someone explain =
me both type.

**What i'm doing, since I'm using mysql for a program***=20
**I execute a query=20
**when i get the query , I'm putting everything in a hash dbm
**Then i read the line i want to, I'm doing so because it is easy to =
overwritte info on the dbm which i really want to.

My.ini Optimization
I'm having some performance issues that seem to be MySQL related.

I am running W2KAS IIS, PHP 5.1.4 and MySQL 5.0.21. Memory or CPU speed do not appear to be the problem.

I'm running a phpBB forum and having difficulties editing posts, the install of phpBB seems to be perfectly okay, but when editing posts it either takes forever and times out or just comes up with a blank page after a couple of minutes.

I notice that mysqld-nt.exe will use up about 30-50% of cpu time when this is going on. Code:

Optimization
i have a little complex query that involves sub queries upto three levels. now thing is that , i think, mysql evaluates the sub queries every time that query is evaluated, whereas, i know that results for the third and 4th level queries are same for some number of queries. can we force mysql to store the result of the subqueries to be used later, instead of reexecuting the query.i studied mysql query optimization but, finally concluded is that things over there are just how mysql optimizes queries, not how can we optimize the query to be performed better. any resources for query optimization, i mean good resources ?

MySQL Optimization??? Help!
im recently been suspended daily by my hosts, saying that im causing a high load on my server with around 500,000 requests daily!!!

Now they are saying "usual" reasons for this are databases, and i run a big phpbb forum. Unfortunatly my knowledge of MySQL is limited, very limited in fact, so i cannot make optimization changes what-so-ever. Iv asked for help at phpbb official support......no answer.... however from browsing, i found "modded" forums can sometimes cause over 100 requests per post, but it can be optimized...... so im looking for help, tips, or anything you guys can give me to combat or fight this problem!

I have access to phpmyadmin, the forum db size is around 100mb, 5,000 members, not all active!

and i also run a topsite list!

if i cannot do this myself im willing to let a mysql expert have a look round, see if thye can fix this, and ill pay them to do so, if must be but my budget is limited, otherwise id just move to a new host

Query Optimization Help Please
I am traversing over a table that holds "adspaces" via two aggregate functions to get the total number of adspaces for a publisher and the number of approved ones. The query is very slow and there must be a way to convert the subqueries into joins or anything else. Would really appreciate it if someone could give me a hand.

PHP

$sqlQuery =      "SELECT a.*, " .
                       "(SELECT COUNT(b.id) FROM ".DB_TABLENAME_ADSPACE." AS b " .
                          "WHERE b.publisher        = a.publisher " .
                           "AND b.status_approval <> '" . STATUS_WAITING . "' " .
                          ") AS num_adspaces_total, " .
                       "(SELECT COUNT(c.id) FROM ".DB_TABLENAME_ADSPACE." AS c " .
                          "WHERE c.publisher        = a.publisher " .
                            "AND c.status_approval  = '" . STATUS_APPROVED . "'" .
                         ") AS num_adspaces_approved " .
                  "FROM `" . DB_TABLENAME_ADSPACE . "` AS a "
                . "WHERE (a.status_changes  = '" . STATUS_WAITING . "' "
                .     "OR a.status_approval = '" . STATUS_WAITING . "') "
                .    "AND a.watchlist       != '" . WATCHLIST_WAITING . "' "
                . ";";

DB Structure/optimization?
I was wondering if anyone could recommend any REALLY good articles/tutorials/whatever on good database structures or optimization?

SQL Query Optimization Help Please
SELECT sess.id, u.id AS user_id FROM
(SELECT s.id, s.session_id
FROM `ll_session` AS s
WHERE s.online = ཈'
) AS sess
LEFT JOIN `ll_user` AS u ON (sess.session_id = u.last_visit)
What I have so far is this:

SELECT s.id, s.session_id, u.id AS user_id FROM `ll_session` as s

LEFT JOIN `ll_user` AS u ON (s.session_id = u.last_visit)
WHERE s.online = ཈'

Query Optimization
On one of our other production servers we have a write heavy database, which currently seems to be utilizing 80% CPU usage and one of our queries seems to be taking quite some time to actually execute.

SELECT *, CONVERT_TZ(date_trade,'GMT','Australia/Sydney') AS date_trade FROM trade_messages WHERE processed = 'NO' ORDER BY ftid ASC LIMIT 200;

200 rows in set (6.33 sec)
The ORDER BY is a necessary part of the query, we could most likely sort this within the code, however we still experience a 3 second query removing the ORDER BY and still seems to scan a lot of rows.

mysql> EXPLAIN SELECT *, CONVERT_TZ(date_trade,'GMT','Australia/Sydney') AS date_trade FROM trade_messages WHERE processed = 'NO' ORDER BY ftid ASC LIMIT 200;
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | trade_messages | ref | processed,searchArgs,proc | processed | 1 | const | 112146 | Using where; Using filesort |
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-----------------------------+
1 row in set (0.66 sec)

EXPLAIN SELECT *, CONVERT_TZ(date_trade,'GMT','Australia/Sydney') AS date_trade FROM trade_messages WHERE processed = 'NO' LIMIT 200; +----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-------------+
| 1 | SIMPLE | trade_messages | ref | processed,searchArgs,proc | processed | 1 | const | 111536 | Using where |
+----+-------------+----------------+------+---------------------------+-----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)
this is a frequent query we execute, and it would be ideal to optimize this further.

Query Optimization Help Please
I have the following scenario:

* I have n categories
* I have m users
* I have x textlinks

Textlinks are referenced to a category by the ll_partof table.

I am willing to fetch every user (advertiser) for every category and output the date of the last textlink purchased, the number of his textlinks in that category and the amount of money spent in that category.

Here is my query so far. It uses subqueries and is therefore very slow. How can this be optimized? Thanks for any help.

Select
c.name as Kategorie,
(SELECT timestamp
FROM ll_textlink as t
WHERE t.advertiser = u.email
ORDER BY timestamp DESC LIMIT 1
) as last_link,
u.email as Email,
u.forename as Vorname,
u.surname as Nachname,
u.company as Firma,
(SELECT count(id) from ll_textlink as t
WHERE t.advertiser = u.email
GROUP BY t.advertiser
) as num_links,
(SELECT sum(current_value) from ll_textlink as t
WHERE t.advertiser = u.email
GROUP BY t.advertiser
) as budget_links,
FROM ll_user as u
INNER JOIN ll_textlink as t ON (t.advertiser = u.email)
INNER JOIN ll_adspace as a ON (a.id = t.adspace)
INNER JOIN ll_partof as p ON (p.adspace = a.id)
INNER JOIN ll_category as c ON (p.category = c.id)

WHERE
t.extension_possible = Ƈ'
AND t.next_period = '-1'
ORDER BY c.id DESC
l

Mysql Optimization
I have table with 7 millions of records , i want to know if some one
can help top optimize the select requests, i created the necessary
indexex but some specific requests still slow.

Query Optimization Help
I've got a query that is trying to find matching rows for two endpoints. Like this...

$sql = "SELECT * FROM my_table WHERE 1234567890 BETWEEN begin_range AND end_range";

The problem is, my_table has about 3.5 million rows, and therefor takes about 4 seconds to run. I've already indexed both the begin_range and end_range fields and I'm just wondering if (other than throwing hardware / memory at the problem) there's anything else I can do?

Is there a better way to query the data?

UNION And Optimization?
My question is about a UNION query to deal with an (annoying) JOIN
over two tables. I am joining over a double column primary key (where the
order of the columns can be changed). This is so slow using a
join, but very fast using a union. How come this is? Code:

Optimization Question
I've got a table T with 20 columns (fixed length) and one column C
which is varchar.

Somehow C has to be varchar.

T may have millions of records.

Is there a way to optimize this situation ?

View Optimization
In my database I have separate four tables and from that four tables i have created one view. Each of the table contains round five lacs of records. So when view gives result it is approximately around fifteen lacs of records,

When I call that view like "select * from MyView where name='vimal'" it takes too long to give me result. And due to this one query my application server load increases by 80%. When I execute this view that time mysql Memory consumption is approximately 80% of total memory. What I can do to optimize the same? What are the other alternatives?

Inner Join Optimization
I have Two tables (ENGINE=MyISAM), each table (t1 and T2) have a
primary key bigint (ID),

I execute the request

Select count(*) from t1 inner join t2 on t1.id =t2.id;

it takes 8 secondes

in eahc tables i have more the 2 million records and more. Can anyone
provide me a suggest to resolve this problem (Changing Mysql
parameters , redesign table...?)

Join Optimization
--B_3141046513_16095154
Content-type: text/plain; charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

I=B9m wondering what (in general) causes a join to use temporary tables and
filesorts...
More specifically, what can I tune to avoid it? I can give more info about
a specific situation if anyone=B9s interested.
All the docs say is that you are likely to get a =B3Using temporary=B2 if you
sort on a different column set than the =B3group by=B2, but I=B9m not doing
that...something else must be triggering it.

WHERE Clause Optimization
if there is a way to optimize 'where' clause with a wild card character, ie - the way star (*) works in unix/linux?
I want to be able to do somsething in this spirit:
WHERE proj != 'P%'
meaning "where data in field proj doesnt start with P"

Join Optimization
what (in general) causes a join to use temporary tables and
filesorts...
More specifically, what can I tune to avoid it? I can give more info about
a specific situation if anyone=B9s interested.

All the docs say is that you are likely to get a =B3Using temporary=B2 if you
sort on a different column set than the =B3group by=B2, but I=B9m not doing
that...something else must be triggering it.

Query Optimization
I am familiar with the VERY basics of MySQL (in other words, I am not an expert...), and I am currently working to create a simple search engine on our website. Users should be able to search a database of over 150,000 sheet music titles by simply inputting a keyword(s) phrase. That phrase should be searched into different tables at the same time but if the keyword phrase includes more than one word, any word need to be searched.

For example, let's say an user is looking for "Brahms violin concerto". The search engine should display all the records that matches all those entered keywords.

Here are the tables that need to be searched:

|title|composer|instruments|description|

and here is the query I am trying to work on (after have split the keyword phrase into separate keywords):

Quote: SELECT title, composer, instruments, description, price FROM mydatabase WHERE (title REGEXP "(^| )brahms( |$)" OR title REGEXP "(^| )violin( |$)" OR title REGEXP "(^| )concerto( |$)") AND (composer REGEXP "(^| )brahms( |$)" OR composer REGEXP "(^| )violin( |$)" OR composer REGEXP "(^| )concerto( |$)") AND ((instruments REGEXP "(^|;| )brahms(;| |$)" OR instruments REGEXP "(^|;| )violin(;| |$)" OR instruments REGEXP "(^|;| )concerto(;| |$)") OR (description REGEXP "(^| )brahms( |$)" OR description REGEXP "(^| )violin( |$)" OR description REGEXP "(^| )concerto( |$)")) order by title like "%brahms%", title like "%violin%", title like "%concerto%", instruments like "%brahms%", instruments like "%violin%", instruments like "%concerto%" The query works, but it takes too long to be executed... over 10 seconds! I am aware that REGEXP (like "LIKE") don't uses indexes, but I cannot find a different solution to match any possible keyword or part of it.

Mysql Optimization
I search through mysql forums but couldn't find a concise place for this topic.

I have a generic table storing a user_ID and some true/false values like 'is_logged_in' etc.

I've discovered that the best way, storage wise, to store those true/false values is through a tinyint(1) with values of 0/1 marking false/true.

I'm wondering what would probably make sense for unique auto incrementing user_ID's. Assume that it may need to get into the millions of users. Any thoughts? Right now I've got it set to int(10) unsigned.

Query Optimization
I have a table, call it 'table1' and a query which looks like:
Code:

SELECT table1.field1, table2.field2 FROM table1, table2 WHERE BINARY table1.username = table2.username AND table2.status = 'Active'

I also have an index set-up on username in table1 and table2. For some reason, mySql does not use the index in table1 for this query, but if I remove the BINARY keyword in the query, the index is used. The type of index is simply 'Index'. Does anyone know why having a case sensetive comparison in the where clause uses index while case-insensetive does not use the index?

Query Optimization
In many of my project. I create an admin welcome page.
On that welcome screen, there is list of all the manageable module names
with their links & the count of number of records under that module.

Example:
Manage Products 104
Manage Customers 235
Manage Orders 430
& so on....

For the count of these records.. I am putting one query for each module..
like "SELECT COUNT(*) FROM products WHERE enumStatus='A'"
For 15 modules, I have to put 15 queries.

EDIT: One module is for one table of the DB

WHERE Statement Optimization
I have a function that tacks together an unknown, variable number of ID numbers that I'm using to filter results in a MySQL. The function constructs a string that looks similar to:

WHERE field = value1 OR field = value2 OR field = value3...

Is there a more optimal way to structure this statment? Can you compare a field against an array, for instance?

Mysql Optimization
just wonder how many columns is the max amount not be an slow table.
Actually I would like to have hundreds of columns, but suppose maybe would be better to have several small, even though that would give me more work updating them.

Query Optimization
This is the query:

$sql = "SELECT DISTINCT(customer_number), upload_date FROM equipment_info WHERE (closed = '0' AND dealer_id ='0') AND ((item NOT LIKE '%Item1%') AND
(item NOT LIKE '%Item2%') AND (item NOT LIKE '%Item3%') AND (item NOT LIKE '%Item4%') AND (item NOT LIKE '%Item5%')
AND item != 'Company Owned Equipment') GROUP BY customer_number ORDER BY upload_date ASC";

yet, I still get this included in the output:

//CI45 Standard Item1 Equip Package, Replacement CI46 LNB Only, Replacement CI33 30 Foot Coax - Yellow

Yet, it was one of the items I wanted it not to match.

MYSQl Optimization
What is Mysql optimization? How I can optimize?

Optimization Advice
I'm having Database connection problem on my VPS server runing SMF forum script.
It often displays "unable to connect to database" notification when there is more than 200 users online (in 15 minute period).
I have raised max mysql connection number from 100 to 150, but that didn't help.
Connections per second number has doubled in last month (but traffic is only slightly higher)

Here are some mysql informations, any advice how to get this numbers to normal values is welcome.

Optimization Of Select
Anybody can help me on optimization some queries
In the slow_query_log the query had the execution time: 12 sec
(Query_time: 12 Lock_time: 0 Rows_sent: 75 Rows_examined: 469546) but if I run the same query in phpMyAdmin i get the time 2,6 seconds.
If I remove the " order by " the query it will be more efficient, but I think there is something wrong if only the ORDER BY it takes like 2 seconds. Any Ideas ?

EXECUTION TIME 2.6183
SELECT cars.id,cars.url,cars.car_name,cars.year, videos.id,videos.brand_id,videos.car_id,videos.name,videos.rewrite FROM cars LEFT JOIN videos ON videos.car_id = cars.id WHERE brandID = '50' ORDER BY year DESC;

EXECUTION TIME 0.0235 sec
SELECT cars.id,cars.url,cars.car_name,cars.year, videos.id,videos.brand_id,videos.car_id,videos.name,videos.rewrite FROM cars LEFT JOIN videos ON videos.car_id = cars.id WHERE brandID = '50' ;

Comparison Optimization
On to my problem. I'm running a query on a table with over 100,000 rows in it. Basically, my query is performing a "similar" check on books that people have logged in their "book list". It looks through the table and checks to see who has the most number of books similar to you. Here's the query:

select count(a.mem_id) as numMatches,m.username,m.mem_id as memberID from book_list a LEFT JOIN book_list_members m ON a.mem_id=m.mem_id where a.book_id in(224,164,30,43,47,1,6,52,90,45,120,270,...,2442) GROUP BY a.mem_id ORDER BY numMatches DESC LIMIT 50

In the in() comparison, this can be anywhere from 1 integer to 1000 integers, it all depends on how many books a user has in their list. I've indexed "book_id", but unfortunately if there are more than say, 100 elements in the IN() comparison, the query takes anywhere from 1-2 seconds to execute. That's a long time when I have hundreds of users hitting a website and running that query. Is there a better way to do this? Is there a way I can "store" this query result somewhere, and only have it updated every couple hours (cached in other words, for immediate access).

SELECT Optimization
I have a table that basically keeps track of the ID numbers of items in 9 other tables.
I do have the need to see all the 'real' information at once and not just the ID numbers.
So, my question is. Is it better to have a SELECT statement with 9 JOINs in it, or to have the SELECT statment only pull the ID numbers and then do separate SELECTs on each ID number.The logic of my brain suggests the first, but I just wanted to check.