teh ghey

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

drocd

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

__________________
230-699

teh ghey

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

drocd

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.

__________________
230-699

tical

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)

NextBigTube

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 ?

__________________

Porn Tubes Hawt Tube,Next Big Tube,
Gay Tube Tube 4 Gay
Amazon.com Discount Finder

react

Your database has a fear of commitment.

__________________
--
react

sysk

I lol'd

__________________
icq: 612510168 / email: [email protected] / php ninja 4 hire

teh ghey

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

teh ghey

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.

smack

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.

__________________
Cry havoc and let slip the dogs of war.

drocd

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.

__________________
230-699

teh ghey

oops mispost

ExLust

Technically challenging.

__________________

BE A PARTNER

quantum-x

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.

__________________
PrettyInCash.com - BoozedGFs.com - TeenGFs.com - JizzGFs.com- MilfUploads.com -

Varius

Yes it does:

show full processlist;

__________________
Skype variuscr - Email varius AT gmail

Antonio