MYSQL :: Time Out Message When Query Large Tables

Can Error Message Show Entire Sql Query Instead Of Excerpt
I'm running MySQL 3.23.58 on Red Hat Linux 9.

In my web server error log I see things like:

[Thu Jan 18 00:33:56 2007] [error] [client 72.86.29.196] DBD::mysql::st
execute failed: You have an error in your SQL syntax near ' (NOW(),
"Setup [precheck] failed for 72.86.29.196 port 1239 [72.86.29.196]
(Con' at line 1 at /var/www/html/blah/mysql-help.pl line 34.

Is there a MySQL setting I can change somewhere so that it will log the
entire SQL query that generated the error, instead of just an excerpt?
It's a lot easier to figure out what went wrong if I can see the whole
query.

Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example:
select myfunc(x,y,z,a,b,c) from events where energy>3;
But as far as I know there is no way of defining your own functions in SQL

Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....

Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables.

My question for performance, as this table grows should it eventually make a new table? If so here is my current select code

select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'

Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?

Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user.
Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details.
Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?

Slow Join On Large Tables
I have two tables:
D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:
SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer
settings.
Adding the following buffer settings only slightly decrerased the time
(~3:00).
key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else.

I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger.

This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen.

Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go.

I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.

One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables.
I need to gather information from various devices (about 500). Each device
has its own Id and some data.

Should I use only one table with an indexed column for the ID and another
column for the data, or should I use 500 tables each with only one column
for the data?

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl=
e categories of entries, the database is quite large about 200 meg.=20

I would like to know if using join tables is more efficient than storing th=
e keys in a varchar field then within the second loop doing a where in (1,2=
,3,4) where the 1,2,3,4 are the stored category keys in the varchar field =
rather than a where in (1), where 1 is the pirmaryID of the entry for insta=
nce ?

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records.

This is very new to me and I am noticing that my queries are really slowwwwww!

What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The
tables have been between 20-100 thousand records size. Now I have a project
where the tables are in the millions of records.

This is very new to me and I am noticing that my queries are really
slowwwwww!

What are the options that I have to speed my queries on the mysql side with
regards to the my.cnf file. I have a fair understanding of
sql optimization and I understand explain. I just want to see if
there is something that I can do with the server also.

Thanks to all.

#Joseph Norris (Perl - what else is here?/Linux/CGI/Mysql)
print @c=map chr $_+100,(6,17,15,16,-68,-3,10,11,16,4,1,14,-68,12,1,14,8,
-68,4,-3,-1,7,1,14,-68,-26,11,15,1,12,4,-68,-22,11,14,14,5,15,-90);

One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this.

My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group.

If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?

Can't Populate Large Tables Through PhpMyAdmin
I'm having trouble woth Yahoo php hosting.
My client wants to move his websites from H-Sphere reseller company where (everything works fine) to YAHOO (he thinks that service and reliability will be much better?).
The problem is with 'products' table where are 15000 records.
Yahoo's phpMyAdmin times out if I try to upload sql/txt file (3.5 mg). I can insert smaller tables pasting script into SQL window - no problem with 4000 records)
I can't do anything with products table (15000 records).
I did not have this problem with our current hosting company.
Under the upload window it says that txt file can be up to 10 mb but mine is only 3.5 mb.

One Large Table Or Several Smaller Tables
From a speed of access standpoint, is it better to have one large table or several smaller tables? I'm in the early stages of development, so I can go either direction with this.

Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down.

I have 4 important tables here (I'm only posting the columns which I use):

`auth`

UID mediumint(8) unsigned PRI NULL auto_increment
username varchar(32) MUL
clan_id mediumint(8) unsigned MUL 0
clan_abbrev varchar(7) MUL
`clans`

ID mediumint(8) unsigned PRI NULL auto_increment
Abbrev varchar(7) MUL
Name varchar(64)
icon_id smallint(5) unsigned 0
`stats`

UID int(10) unsigned PRI 0
kills mediumint(9) NULL
`usernames`

UID int(10) unsigned 0
ID int(10) unsigned PRI NULL auto_increment
Name varchar(32)
Uses mediumint(9) 0
`auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`.

I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`.
The result should be ordered by `Uses` (from `usernames`) -- highest to lowest.

The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type).

Here's what I managed to hammer out with my limited knowledge of SQL:

(
SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `clans` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND clans.ID = auth.clan_id
AND stats.UID = auth.UID
)
UNION (

SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND auth.clan_id =0
AND stats.UID = auth.UID
)
ORDER BY `Uses` DESC
It works great, but the average query takes around 6s (these tables have several thousand entries in each).

Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.

Perhaps someone can help me with indexes here; I was under
the impression that the index for the tables are cached in memory, and
therefore permitted "instant" searchability, without having to retrieve data
from each of the rows of the DB. Is there a startup parameter, or
something in the mysql.ini file, that must be set to allow for this? I have
the index configured properly, and have made sure that the query uses there
parameters in the where clause in the same order that they appear in the
index. Code:

Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL.
I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools.
The performance decreased gradually and crashed at ~60% completion.
Now I'm planning to use xml2sql tools to convert the dumps to txt format.
Then I'm planning to load this using mysqlimport.
I would like to run this load faster, and on Oracle, I would use the IMP parameters
such as INDEXES=n and so on. What are the recommended steps when using mysql?
Should I target any of the system variables?
I have a 4G ram on the server and can use all of it for the load.
Any pointers?

Mysql_fetch_array() And Large Query
I am running a SELECT query on a table of about 1.6 million records.

The number of rows I get back is about 1.5million. SQL runs fine, in command line and php, but when I try to move data from 'resource' to an array with mysql_fetch_array() my browser stalls.

So my question is, am I overflowing the buffer somewhere, and if anybody has a clue how to fix this.

Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing

Noob: Select Large Query
If I had to select large number of records, say 1 million from the mysql server. What problems would I be facing? MySQL connection timeout, network latency?

Anyone has done a large simple mysql select query and what problem did you face? I am not doing any joins.

Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username
FROM (( " . phpbb_forums . " f
LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id )
LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id)
ORDER BY f.cat_id, f.forum_order";

Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions?

The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?

Batch Query Or Large List Of IN() Arguments?
I'm receiving a potentially large list of productID numbers from an external data source.

Each productID has a number of partID's associated with it and those partID's are stored in my database.

I would like to create a list of products sorted by the number of parts each has.

If I only have one table called "part" which stores a partID and its associated productID, which of the following methods would be more efficient assuming the application and DB are on the same server.

Option A)

SELECT productID, Count(*) counter
FROM part
WHERE productID
IN(large list of productID's received, maybe 1 or >10000)
GROUP BY productID
ORDER BY counter DESC
LIMIT ?, 10

Option B)

Execute a batch query of prepared statements (SELECT COUNT(*) FROM part WHERE productID=?) for each product in the received product list then sort the returned results in the Java app that is making the query.

Option C)

???

Retreiving Large Query Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of
records. Because I need to present them in GUI, I returieve the
results in chunks using LIMIT, for example - get first 100, then the
range 100-2000 and so on.

The problem is as follows: in the first chunk, MySQL uses one strategy
to fetch the results, and in the following chunks - a different
strategy.
This means that records from the subsequent queries might have records
that already appeared in the first query or that some records will be
left out.

For performance issues it is a problem to add a unique secondary
sorting criteria (like id) to the query.

Is there a clean way to force MySQL to relate to the first (initial)
query result set?

Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do:

There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns.

The most common query is SELECT data FROM table WHERE n1 = ?

The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column.

Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset:

SELECT COUNT(1) AS total FROM table WHERE n1 = 3;
index = random number from 1 to total
SELECT data FROM table WHERE n1 = 3 LIMIT index,1;

How can I speed this up? I need it to take less than half a second if possible. Thank you.

Using Limit On Grouped Query With Large Number Or Records
I have a query that use a "group by" clause that returns 600+ queries

however if I add a LIMIT 500,50

It returns 0 records. I tested LIMIT 450,50 and it returned only 34 records.

Is there something limiting the limit? Is there an option in mysql that can increase this limit?

Update 3 Tables At A Time
i have 3 tables namely--- artist, journalist and songs.
i try to update the flags in artist and journalist and try to update few fields from songs by firing foloowing qurries.

update songs set sname='lala', sname2='my baby' where artistid=6;
update artist set statusartist='new',flag1='updated' where artistid=6;
update journalist set statusjourn='new',flag1='updated' where journid=4;

when i fire the above querries individually they are excuted without any error.
when i try to run these querries through my JSP application only the songs data is updated and the rest 2 tables are not.

i tried to write 1 combined query including all 3 tables for this,but still it didnt work.
can u please tell me a solution for this.

Time Zone Name Tables
Having some troubles with an installtion on AIX. We've installed mysql alongside our own product which uses a mysql db. Checking the logs we get an error:

051111 16:52:43 [Warning] Can't open and lock time zone table: Incorrect
information in file: './mysql/time_zone_name.frm' trying to live without them

Tried to read the mysql db and got:

Didn't find any fields in table 'help_keyword'
Didn't find any fields in table 'time_zone'
Didn't find any fields in table 'time_zone_name'
Didn't find any fields in table 'time_zone_transition'
Didn't find any fields in table 'time_zone_transition_type'

and trying to read the 'time_zone_name' table gives:

ERROR 1033 (HY000): Incorrect information in file: './mysql/time_zone_name.frm'

The timezone we have set is CST6CDT, asopposed to one of our other AIX boxes which is GMT0BST. My question is should this be causing a problem, and if so is there a way around it without changing timezones on the box?

Copying Tables Every Time They're Updated
I'm got two tables which need to kept upto date with each other. Is there a command i can cron to copy all the updated entries?

Any Way To Insert Data Into Two Tables At The Same Time?
what I have is a table called `order` and a table called `lmcharge`.
a person inputs data from a form and i need to take the data from both and some goes into one table, some goes into another
here is an example:

order values:
student_id, class_id, order_type, date, status
//student_id, class_id, order_type will be from the form

lmcharge values:
student_id, order_id, lm_number
//student_id, and lm_number will also be from the form but order_id will have to be from the order table

anyone have a clue?

Access Tables From 2 Clients At The Same Time
if I want to use MyISAM tables in a DB from 2 different clients (from an application) which can manipulate data at the same time.

This works good for about a month but now I have an error that a fieldvalue of a record was suddenly changed but there is no way to do that by the application.
The field has got the data of an other record (so I have this twice), but this value can only exists once. The rest of the data of that record is still correct.

Does anyone know what the problem is?

Time Difference Between Records From Two Tables
I have two tables with data from two separate data loggers. The timestamps of the records are not perfectly synchronised (eg. 2005-07-11 22:50:00 and 2005-07-11 22:49:58). All data must be joined to one table and all records that are out of sync by more then 30 seconds must be rejected. I tried somethig like this: Code:

Time Query - Please Help?
Iâ€™m completely baffled by a query that I thought I had working yesterday, but I canâ€™t get it working today.

Hereâ€™s the screnario:

Iâ€™m publishing a story to the web and I donâ€™t wonâ€™t it to be published live until:

-The current date (type: date) is less or equal to now
-The current time (type: time) is less or equal to now

So hereâ€™s my query:

select * from cms_stories
WHERE section = 'news'
AND published_web_date <= NOW() OR published_web_date IS NULL
AND (published_web_time <= NOW() OR published_web_time IS NULL)
ORDER BY story_id
DESC LIMIT 1
Now the query works for the published_web_date, but not for the published_web_time. It seems to ignore that as you can see from the screenshot returned below from that query.

Query Time
Is there a function out there to determine the length of time a particular query took

Take Long Time To Run A Query!
I have a webserver that I just use for a webapplication. PHP, Apache and MySQL is installed on the server.

The size of MySQL database is 10 GB. End inn it is millions of rows.

I wonder where I search what is the normail time the data to return? In my case it takes from 20 sec to 2 min to run a query!

Time Query Question
I'm trying to pull dates from a db that are newer than 6 months old. I've been testing with 1 day, since there's nothing in the db that's older than 6 months.

query

PHP

$DATEsql = "SELECT DISTINCT DATE_FORMAT(time_in, '%m/%e/%Y') as time ";
$DATEsql .="FROM login ";
$DATEaql .="WHERE time_in > CURDATE() - INTERVAL 1 DAY ";
$DATEsql .="ORDER BY time DESC ";

that is giving me the same results as

PHP

$DATEsql = "SELECT DISTINCT DATE_FORMAT(time_in, '%m/%e/%Y') as time ";
$DATEsql .="FROM login ";
$DATEaql .="WHERE time_in < CURDATE() - INTERVAL 1 DAY ";
$DATEsql .="ORDER BY time DESC ";

This is what I'm interpreting as what I need to do according to the MySql manual.

How To Get Time Execution Of A Query?
Do you know how to get the time that a query executes? Is there a formula on how to get it?

Example:

Note: I have 5000 data in the table cars

$result = mysql_query("Select * from cars");

Question: How to get time of executing the query above?

Execute Time Of A Query
I'm running a SELECT * query on a table with over 3,000 rows. I need to know the execute time on the query.

PHPMyAdmin shows it to me, but because they automatically add a LIMIT 0, 30 I suspect the time is only for those 30 records. It also has a feature to prevent web server timeouts that prevents me from setting the LIMIT to 0, 3100.

BTW, I'm programming in PHP. Does the time to run the query get automatically passed when PHP sends the query to MySQL?

<? PHP
$Query = "select * from my_table where 1";
$Result = MYSQL_QUERY($Query);
?>

Using Time Functions Within A Query
I'm currently working on a script that searches through a database of restauarant information. One of the things it does is allow the user to search only for restaurants that are open at the time of the search, however i get errors when trying to implement this.

Here is the query im using:

How To Get Query Execution Time
How to get query execution time in "mysqlquery.log" files

I am using:

Linux 7.3
mysql 4.1

How to display the query execution time for each and every query in log files.
i have configured "slow-log-query" and "mysqlquery.log" in "my.cnf" is pasted below.

log = /var/lib/mysql/mysqlquery.log
log-slow-queries = /var/lib/mysql/slowquery.log
long-query-time = 0
slow-launch-time = 1

then i can get a output in "mysqlquery.log" like,

070830 9:24:29 2 Connect root@localhost on
2 Init DB jbdatabase
2 Query select count(*) from vacancy1_table
2 Query select category,listcategory from category_t
able order by category
2 Quit

but i need to display the "timetaken of each end every executed query" along with the query in the above logfile like,

E.g:select category,listcategory from category_table order by category(0.03 secs).

Query Execution Time
I am using mysql for my shopping site,Performance of site very slow then I used mysql_stat php function to find out howmany slow queries,it shows 20.

Now my question is any function in mysql(or)PHP to show 20 slow queries.

Limiting Query Time
Is it possible to limit the query time in MySQL (3.x or 4.0)? For
example, I'd like to have any query that takes more than a specified
number of seconds just quit automatically. Seems dumb, but on a web
site, nobody is going to wait minutes for a query to return so they
refresh anyway. So on a busy server, MySQL ends up with several queries
running that all take a long time to finish which compound to make it
even slower. A simple time limit would solve the problem. Yes, I know
that the queries should take less time, but again, on a busy server,
sometimes the longer queries do take a long time (10 minutes or more)
to complete.