Any SQL pros here? question for you...
 

The content management system im using for my site is creating temp tables and copying them to disk with every query its slowing down my site.

So I only have 100 users on my site but my site is fucking sloow.
How SHOULD queries be made? Should it be doing this in memory?

Is it hard to switch this from copying things to disk to copying them to memory?
Im going to complain to them but want a better idea of what Im talking about first.
thanks

There could be numerous related reasons why this is happening. Your mysql settings may need to be tuned, queries optimized, tables indexed, etc.

thanks. but if its creating temp tables and copying them to disk then thats bad, right?

Yes. Ideally you'd want mysql to write the table to memory instead of disk. In some cases mysql is forced to write to disk though. You can try increasing mysql variables max_heap_table_size and tmp_table_size. You could also find the specific queries and do an EXPLAIN on them to find out what's really going on and find a solution.

why are you creating temp tables with every query? seems like something that would be done at the end of the day or something to handle cumulative amounts of data (vs every query)

I am confused by your statement "creating temp tables and copying them to disk". Temp tables are created explicitly. In Sybase it is "CREATE #tablename", in MySQL, it appears to be "CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name"

If your CMS is running queries (rather an SQL script) that creates a temp table, then there must be a good reason (or bad programming). e.g. A lot of times temp tables are created when it is necessary to iterate the rows.

Can post the queries to provide a better understanding of the issue ?

Your database has a fear of commitment.

I lol'd:1orglaugh:1orglaugh

its a dating site, so theres tons of queries every minute

Here are a couple of them:

| 1138108 | (my-db-username-hidden) | localhost |(my-db-name-hidden) | Query | 0 | Copying to tmp table | SELECT m.* , data_age, data_height, data_weight, data_sexually, data_seekingage_from, data_seekingag |

| 1138109 | (my-db-username-hidden)| localhost | (my-db-name-hidden) | Query | 0 | Copying to tmp table | SELECT m.* , data_age, data_height, data_weight, data_sexually, data_seekingage_from, data_seekingag |


Unfortunately the mysql processlist doesn't show the entire query to provide for you.

it's not necessarily a bad thing. it depends on how you will be using the tables and what they're building off of. it's most certainly not optimal if it's slowing down your site that much.

i would have to look at the joins to really tell you for certain, but a couple months ago i ran in to a similar problem with building temporary tables. the query was taking 30 - 45 seconds to build, so what i ended up doing was creating a stored procedure with a dynamic sql statement in it, then exchanging one of my tables for a normalized look up table index off of integer values. that ended up taking it from 30 - 45 seconds average down to milliseconds.

it's all about normalization, good indexing, and smart joins.

Have your host/server admin/whoever enable the mysql slow query log. Then you will have a full list of mysql queries that you can work on optimizing.

The answers you need are in this thread.

PS; tical and NextBigTube clearly don't understand your problem, so please ignore their posts.

oops mispost

Technically challenging.

From the looks of the query log, you need to index some of those tables a little better.
If those select queries are using the filesystem, it's probably because of a lack of index.

You need to run an EXPLAIN query.

Put "EXPLAIN" in front of one of the lagging queries, ie:

EXPLAIN SELECT m.* , data_age, data_height, data_weight, data_sexually, data_seekingage_from, data_seekingag..........

This will break down the joins, subqueries, etc, and will show you exactly which relationship / index is using the file system. From there, make sure it's indexed, and you'll probably be ok.

If not, you'll need to rewrite the query a little more efficiently.

Yes it does:

show full processlist;

:1orglaugh:1orglaugh:1orglaugh