MYSQL :: Small Favor Gone Wrong

Handler_read_rnd_next = 13k On SMALL Db
I have two very small DBs.

One for a little number game, which only 6 people hit 1-3 times a day. The other is for a prototype of a game I'm working on, and there are no more than 3 users on this.

Using phpmyadmin, I check the page with runtime info and I see

Handler_read_rnd_next 13 k

If I reload this page a couple times, it goes up to 14k.

Most of the queries I use look like

Code:

SELECT c.color, b.percent
FROM tbl_creatures a, tbl_creatures_colors b, tbl_colors c
WHERE a.creature_id =1
AND a.creature_id = b.creature_id
AND b.color_id = c.color_id

And the EXPLAIN SQL for these comes up with

Code:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a const PRIMARY PRIMARY 4 const 1 Using index
1 SIMPLE b ref PRIMARY,color_idPRIMARY 4 const 2
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 hq_proto_creatures.b.color_id 1

To me, this all looks fine. I can't tell if all the Handler_read_rnd_next is due to the phpmyadmin status checks or not.

View Replies !

Small Databases
I am wondering if having a database for the client back office, a database for the static website and a database for website stats would be a good idea.

The client database would hold credit card info, user info, reg info, project info, client messages and more.
The website database would carry all the content for the pages of the public website like about us, contact, home page, news and such.
The website stats database would keep track of user agents, new hits, returning hits, keyword searches made on the client websites and all that jazz.
Should I make this all ONE giant database or three smaller database?

View Replies !

One Big Table Or Many Small Ones?
I am designing an interactive website consisting of a few pages and I want to track individual user progress. I was wondering if it would be better to create one big table for all values on all pages (Which may have a lot of columns, easily hundred or more), or created a separate table for each page (so I could have some 10 tables with 10 columns or more). Or does it not matter?

View Replies !

Big Select Vs Small Ones
I have an array of 10 IDs.
I need to query the db for values associated with these 10 IDs.

In a lot of places, I have seen people using a php foreach like this

foreach (ids in array)
{
query = SELECT values FROM tables WHERE id = [id 1 in array]
do query
store values
}

we could build one big select statement like this:

query = SELECT values FROM tables WHERE id = [id 1 in array] OR [id 2 in array] OR [id 3 in array].... until we have ids.

View Replies !

One Big Database Or A Lot Of Small Ones?
My boss is implementing a system where a lot of different sites will be using the same exact database structure. He has it in his head that things would be simpler with one database shared by hundreds, possibly thousands of sites. I feel like each site deserves it's own database, even if it involves more maintainence.

View Replies !

Small Query Optimization
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.The query is:

Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%'

I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.

View Replies !

Backup A Small Database.
I'm looking to backup a small mysql database. The problem is mysqldump does not exist on the server I'm trying to export the database from.

Does a php script exist which I could use to do essentially the same thing as mysqldump?

View Replies !

Small Binary Data
Can anybody tell me what way is best to save a small binary data (16 Byte) in MySQL?

View Replies !

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?

View Replies !

MySQL Upload Small Files
I'm uploading small files (>10K) and mysql is deleting it. The one field is a blob field and everytime i test upload a file thru phpMyAdmin within 2 seconds mysql deletes the row. Theres nothing in mysqld.log, there another log i should be checking out?

View Replies !

One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db.
My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?

View Replies !

Structure Of Small Social Network
I need to setup a social network type mysql database, but not as intense as friendster, thefacebook, myspace, etc.

i have a structure set up but it think it's weak and extremely limited:

Code:

+---------+-----------+
| user_id | friend_id |
+---------+-----------+
| 5 | 4 |
| 1 | 7 |
+---------+-----------+

i fear it's going to be a lot of redundant IDs in a column, which i think could be a problem when retreiving friends:

Code:

+---------+-----------+
| user_id | friend_id |
+---------+-----------+
| 5 | 4 |
| 1 | 7 |
| 1 | 9 |
| 7 | 9 |
+---------+-----------+

i want to do a "bob is friends with john" and a "jackie knows sally through richard" (aka friend of a friend). that's it and nothing more. how do i structure mySQL database to be able to this?

View Replies !

One Huge Table As Appose To Many Small Ones
i have a very large XML DB (60gb+ and growing on a daily basis) which holds complete life cycles of stock option , i used to hold it all in folders with each folder named like the option paperId so it seemed logical to me to hold it now in diffrent tables for each paper even tough the are all the same exact table , now i'm having some problems with hibernate (one of the biggest reasons i left XML) and so many tables , is it more effiecent to keeeping data like that ?

ofcours i will have to change the PK from datetime to paperId+DateTime if i decide to change to one big table ... how can i calculate diffrence in retrieving data ?

View Replies !

A Huge Database Or A Lot Of Small Databases
I have a question regarding database performance.

Which of these two designs would be better:

-Having one huge database with 50 tables or so (each table having millions of rows)

or

-Having a lot of databases with the same amount of tables, but each table would have only thousands of rows

*The mysqlserver is running on a P4 3.0 ghz with 1 gb in ram, but I may upgrade my server soon.

*Privileges to create databases is not a problem since I own the server

View Replies !

Several Small Calls Vs One Large Call
I am working on a php interface to a mySQL db which will pull lots of rows from the database. Assuming I am selecting a hundred rows by their auto_incremented ID's, is it better to do this with a loop of 100 "SELECT ... WHERE id=" calls, or to do it with one single "SELECT ... WHERE id IN(...)" call, with IN() holding a list of the hundred items.

View Replies !

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?

View Replies !

Help With A Small Query Optimization Using An Index. THANKS!
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.

The query is:

Quote:

select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%'

I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.

View Replies !

Small Database, High Load
I have a very heavy mysql database used website. Data is constantly being updated and at peak there is well over 1,000 people online, after it hits the 1,000 mark, the website starts to lag pretty bad.

My database is only small at around 90mb. We have a web server for the files, and a database server for the database. The load on the web server is fine. But the database server is pretty high.

Would replication reduce lag and load? if i was too add a second database server?

View Replies !

Inserting Small Image Directly Into Database
How do you insert an image into a table directly using something like data studio?
I have tried googling it and it seems all the examples are using a language to do it for you. The images are between 2-4k and the table is just an int column followed by a blob column. I just thought the statement would go like

INSERT INTO sometable VALUES (1,'image.jpg');

The web suggests uploading and downloading in PHP (which im using) but im building the data first using data studio then just displaying the table information using PHP.

Before i go into displaying the information on the page i just wanted to make sure the insert query is correct.

View Replies !

Small SQL & Php Webpage Field Database Task
In a webpage, imagine a little section where the user fills in his/her contact details to register. Among this section is a field called "Country". The task is to make this field automatically generate the persons current country of residence. I am told, that the IP ranges and the country to which they belong is in a CSV .xls file. I am also told that the best way to do this is to:

1) Insert the CSV into database table (MySQL).
2) Use php to get that country name onto the website field.

View Replies !

Large .sql File (break Down To Small Parts)
i have an oscommerce installation that generates a 200mb sql backup file. i want to try and rebuild the installation on my local pc (localhost) to emulate and play with, unfortunately the 200mb file is difficult to handle. phpymadmin seems to crash.

i have console access on the host however.

is there a way i can break down the sql file into smaller increments for easier uploading?

View Replies !

How To Reset Yahoo Small Business MySQL Database
I am a new user of "Yahoo Small Business" web hosting service and I changed
a lot of settings in my "MySQL Database" using "phpMyAdmin" utility during my
learning period. Now, I want to reset my Yahoo Small Business MySQL Database
to default settings or to reinstall it (I want it to be the same as it was newly
activated).

Note: I tried to repair my database with Yahoo's "Database Setup" page and the
message "Database scheduled for repair" appeared for a few days but nothing
happened. And, I can not login to my database using "phpMyAdmin" utility.

If you can help me to reset my "Yahoo Small Business - MySQL Database" I will
be very pleased.

View Replies !

Wrong Definition
Is this a wrong definition of a mysql db? (no defaults defined) :

`date1` int(10) default NULL,
`date2` int(10) default NULL,
`num_jokes` int(8) unsigned NOT NULL,
`title` varchar(255) NOT NULL,
`path` text NOT NULL,
etc...
etc...

View Replies !

Wrong Results
I have the following select statement

Code
$query_Recordset1 = "SELECT * FROM data where('$beds like $options')";

the $beds variable is a text field, and the $options variable is a drop-down menu. Technically the query works, but when i put in 775 and click ZIP in the drop-down box it shows zip codes that dont even start with a 7, much less a 775

View Replies !

Sorting Is Wrong
I have some products in the database and trying to sort them ascend. by their prices. But for example, the price $14.00 comes before than $8.00. How can I correct the problem? As I understood, mysql only checks for the first digit. Not the whole.

View Replies !

Wrong With My Syntax
if I have the following tables in my database:

+-----------------+
| Tables_in_test |
+-----------------+
| temp |
| tempallclaims |
+-----------------+
2 rows in set (0.00 sec)

shouldn't the following statement:

"select * into temp from tempallclaims;" be a valid SQL query? I keep getting
an error message. i've also tried a non-existent table name:
"select * into tempBack from tempallclaims;" but its a no-go as well.

View Replies !

Used Wrong Password
I installed MySQL through XAMPP application to Windows XP SP2. All was well, I opened the ADMIN of MySQL from the XAMPP Control Panel and received a drop down box asking for user name and password. For username I used my own name. I have since found out that I should have used "root".I went through a whole bunch of problems with "access violation messages which are still coming, although now I know how to shut them off, but I cannot access MySQL at all to change the password to what it was supposed to be.In essence, I cannot use it at all, and I don't know how to make the fix. Could someone please advise?

View Replies !

Something Is Seriously Wrong In 5.1.x Releases
I am runnging 5.0.67 with no problems, but every time I upgrade to some of the 5.1. releases in 24 hours my mysql freezes and I get too many connections. After trying to quit it never stops, I have to reboot.

I can not see what is wrong. And ideas?

I am running Windows 2003 with myODBC 5.1 and mySQL 5.1.28 (until it freezes, than I am back to 5.0.67) :-(

View Replies !

Any Ideas What's Wrong With My SQL?
SELECT * FROM table1 t1 WHERE MONTH(theDate) = 06
INNER JOIN table2 t2
ON t2.eid = t1.id
ORDER BY theDate DESC LIMIT 0, 10

View Replies !

What Is Wrong With This Syntax
I must be staring at this query to hard. This query is called within a block of PHP code.

"UPDATE business SET
businessName='$business',
profile='$profile',
catId='$cat',
WHERE id='$id'";
I get an error that says the following:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=ƈ'' at line 5"

The query looks good to me. The id is supposed to = 2.

View Replies !

What's Wrong With This Insert?
INSERT INTO zips (zip, lat, long, city, state, county, type)
VALUES (?','+40.922326','-072.637078','HOLTSVILLE','NY','SUFFOLK','UNIQUE');

View Replies !

Can Anyone Tell Me What's Wrong With This Query Please
PHP
$kkdx = @mysql_query("select id, ".$kli." from secteur order by position") or die(mysql_error());
while ($kkdx1=mysql_fetch_array($kkdx)) {
print '<h5>'.$kkdx1[1].'</h5>'
$kat_details=@mysql_query("
select
members.lastname,
members.firstname,
members.title, // title = member's field
members.localization // localization = member's field
from
members,
localization, // localization = seperate table
title // title = seperate table
where
members.Secteur = ".$kkdx1[0]." and
members.localization = localization.id
order by
localization.position,
title.position
");
while ($abc=mysql_fetch_array($kat_details)) {
print $abc['lastname'].' '.$abc['firstname'];
}
}

I am getting much more rows than what I should get, not ordered as I want, etc ... totally messed up

View Replies !

What's Wrong With SELECT *?
This has been bothering me for a while now, so I decided to come here for some answers.

I was told very early on not to use SELECT * and to always specify each field that the SELECT statement should target. This makes sense to me since MySQL doesn't need to do the extra work of going to see what fields are available then finding the data... so its faster.

But what if I know ill be selecting every field in the table. And what if that table has 10+ fields. It becomes a pain having to type out all those fields just for a ( minor?) boost in query speed.

This becomes even more of a problem when I have multiple queries either on their own or in stored procedures that are all pulling out the same data. If this is the case and I need to add/rename/remove a field from the table then I have to hunt down every SELECT that is targeting that table.

Basically im looking for good reasons why I should/shouldn't use SELECT *.

View Replies !

Mysqlbinlog Wrong Output
I get following output from "mysqlbinlog mylog-bin-file.001":

# at 4
#030818 13:38:00 server id 1 Start: binlog v 4, server v created
700101 1:00:00

The file mylog-bin-file.001 contains many queries but they don't appear
in the output from mysqlbinlog.

What is wrong?

View Replies !

Wrong Syntax Constraint
1)I'm trying to create a new table but the creation fails because I
insert as a wrong syntax of the keyword 'constraint'.

For example...
TABLE xxxx
field 1 cod
field 2 name
field 3 is_died (boolean)
field 4 died_date (date)

I would like to chek if is_died == true then died_date must not has
NULL value and viceversa if is_died == false then died_date must has
NULL value.

2) I would like manage constraint between two table but I have no idea
about it.

View Replies !

Whats Wrong With Using A BLOB?
the previous programmer for the html/php site i now have the reins of used a mysql database, but for SOME reason he decided to post product images to a file directory--rather than store it in a BLOB. i haven't had ANY experience with BLOBs so i'm wondering if he knew something that i didn't. the only problem i could possibly foresee is that we watermark our images. but the original photo is NOT watermarked--it is done in realtime by another page that hands the photos out on request.

so, is there any reason i shouldn't be using BLOBs to store images? currently, a problem that has come up quite frequently is when we rename an item code, that the function he created to rename the image is not working. now, i have no idea why he didn't assign image names based on the autonumber id (which would never change) so i'm just wondering if he really didn't put too much thought into this area. what do you guys think?

View Replies !

INSERT Inserts Wrong Value
As the title says, an incorrect value is being stored in my table when doing an SQL query.

INSERT INTO picks (id,player_id,position) VALUES(3,20050919104352853297,1)

None of those is the primary key, yet the middle number gets messed up when stored. The current field type is varchar(35), latin1_swedish_ci.

The value that's being inserted is:

20050919104352854016, so just the last 4 numbers are changing.

View Replies !

Load Data - Is This A Bug Or What Did I Wrong?
I'm using MySQL 5.0.27 on Windows. I'm not sure whether this is a bug or if I do something wrong. Hence, I'm posting here.

I think it's a bug - but one never knows.
I made up some test scenario. It consist of this datafile
c:lb.csv
---
1,0f10
2,0f20
3,0f30
4,0f40
5,0f50
---
and this script
---
create database if not exists load_bug;
use load_bug;
create table if not exists lb (
i int not null primary key,
s varchar(20) unique not null
);

load data local infile "c:lb.csv" into table lb fields terminated by ",";

select i from lb where s="0f10";
---
The set remains empty. select * from lb; gives:
+---+-------+
| i | s |
+---+-------+
| | 0f10
| | 0f20
| | 0f30
| | 0f40
| | 0f50
+---+-------+

Furthermore, if I reverse the sequence of i and s in the table everything works as expected. If I add another data element (j int) it works too.

View Replies !

What's Wrong With This Query String?
$query = "select * from categories where name like "%$trimmed%" OR description like "%$trimmed%" OR dotcom like "%$trimmed%" AND nichelink like "WD%" order by name";

I want to get search matches in the category fields of name, description, and dotcom, but only if the field nichelink also matches "WD".

View Replies !

What Is Wrong Here With FROM_UNIXTIME + UNIX_TIMESTAMP
I am using gentoo-AMD64 and the MYSQL server version provided by gentoo is currently 4.0.24

When I am inserting a DATETIME which is in that magic gap during the change from summer time to normal time, MySQL adds a magic hour. My bug, or a MySQL bug?

This is what I am doing:
######################################
mysql> create table a (f1 DATETIME);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into a VALUES (FROM_UNIXTIME(1130631910));
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+---------------------+
| f1 |
+---------------------+
| 2005-10-30 02:25:10 |
+---------------------+
1 row in set (0.00 sec)

mysql> select UNIX_TIMESTAMP(f1) from a;
+--------------------+
| UNIX_TIMESTAMP(f1) |
+--------------------+
| 1130635510 |
+--------------------+
1 row in set (0.00 sec)
######################################

I inserted 1130631910 and later retrieve 1130635510?

View Replies !

Whats Wrong With This Query. Using Now(). >= And <=
I have a table with these fields "bought" and "expires" and the date for these are "2006-06-10 10:52:35" and "2006-06-12 10:52:39". but when i run this query

$getvoucher = "SELECT bought, expires, NOW() FROM book_voucher WHERE bought >= 'NOW()' AND expires <= 'NOW()'";

It doesn't find a record.

$getvoucher1 = "SELECT * FROM book_voucher WHERE expires <= 'NOW()'";
$getvoucher = "SELECT * FROM book_voucher WHERE bought >= 'NOW()'";

Its finding a row for the first statement but not the second.

View Replies !

Whats Wrong With My Query?
Whats wrong with this?

SELECT * FROM listings WHERE title REGEXP "^$this->letter" ORDER BY title ASC

$this->letter is the variable .....

View Replies !

Something Gotta Be Wrong With This Query.
[sql]SELECT DISTINCT ID, post_title FROM wp_posts, wp_post2cat WHERE category_id = 1 OR 5 AND post_status = 'publish' ORDER BY rand() LIMIT 5[/sql]

Uhm. Something has to be wrong with this query as it is even selecting posts with post_status not equal to publish and posts from cats other than 1 or 5.

View Replies !

Is This A Wrong Definition Of A Mysql Db?
Is this a wrong definition of a mysql db? (no defaults defined) :

`date1` int(10) default NULL,
`date2` int(10) default NULL,
`num_jokes` int(8) unsigned NOT NULL,
`title` varchar(255) NOT NULL,
`path` text NOT NULL,

View Replies !

Somethings Wrong With The Mysql_num_rows
I was making a start page, where one must register a personal code, as in password, to proceed to the main site.

to check if the code has already been used by someone else, i used the mysql_num_rows() function, but somethings making it fail...

this is where i define a function wich is causing the whole jam. ive tried echoeing here and there, turns out the line with the error is the one with the
$count = mysql_num_rows($result) or die(mysql_error());...

PHP:

<?php
    session_start();
    require_once('vars.php');

    //connectie maken met DB
            $db = mysql_connect("localhost", "dieetopmaat_be", "dieetDB") or die(mysql_error());
            mysql_select_db(dieetopmaat_be, $db) or die(mysql_error());

    function check_presence($table, $row, $value){

        $sql = "SELECT *     FROM $table
                            WHERE $row = '$value'";
        $result = mysql_query($sql) or die(mysql_error());

        $count = mysql_num_rows($result) or die(mysql_error());

    if($count >= 1)
        {
        return TRUE;
    }else
        {
        return FALSE;
    }

View Replies !

Correct Wrong Spelling
Is it possible with mySQL to get results when the spelling is not exactly correct. E.g. a visitor fills out the field with Amstrdam (note that the "e" is missing). Is there a function for mySQL that also shows results similar to Amstrdam:
-Amsterdam
-Amsteldam

View Replies !

What's Wrong With This Stored Procedure?
I have this stored procedure
CREATE PROCEDURE `documents`.`insert_documents_by_id`(in_DocumentId integer, in_DocumentLanguage varchar(255))
BEGIN
IF in_DocumentId= -1 THEN
insert into document(language) values( in_DocumentLanguage );
select LAST_INSERT_ID();
ELSE
update document set language=in_DocumentLanguage where id=in_DocumentId;
select in_id;
END IF;
END $$
I keep getting the following error when I execute it
"No database selected"

What's wrong... the database is already selected and it's called documents

View Replies !

Whats Wrong With This Syntax?
Here is my query:

UPDATE 06_acct_pg_status SET status = 3, last_update = NOW() WHERE pg_id = 10;

It worked when it was:

UPDATE 06_acct_pg_status SET status = 3 WHERE pg_id = 10;

Adding "last_update = NOW()" is what is problematic.

View Replies !

Whats Wrong With This Sql Query...
$query = "SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,staff.staffname,
amount.amount,
DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1,
DATE_FORMAT(access.expdate, '%d-%m-%Y ')As formatteddate2
FROM dealerdtl,amount,staff,dealer,loguser,access where access.icnumber=loguser.icnumber
AND dealer.dealercode=dealerdtl.dealercode AND staff.sid=amount.staffcode
AND loguser.icnumber = '$custic'";
This query select multiple rows, how to configure it for one row...

View Replies !

What's Wrong With This Store Function?
first attempt at it:

DELIMITER $$

DROP FUNCTION IF EXISTS `db2`.`difference`$$

CREATE FUNCTION `db2`.`difference` (n1 varchar(10), n2 varchar(10))
RETURNS int
BEGIN

declare a1 int;
declare a2 int;
declare a3 int;
declare a4 int;
set a1=substring(n1,2,3);
set a2=substring(n2,2,3);
set a3=a1-a2;
set a4=sqrt(a3*a3);
return a4;

END$$

DELIMITER ;
error message:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare a2 int;
declare a3 int;
declare a4 int;
set a1=substring(n1,2,3);
set a2' at line 1
(0 ms taken)

View Replies !

Wrong Date Being Inserted
I'm trying to insert (via a temp table) a date from a mysql dump (db-A into db-B). Both db A & B use the same column types: DATETIME.

So, when I try to insert a date (for example: 2007-02-12 10:08:47) it gets inserted with today's date.

in my temp table, I have tried both datetime and varchar with no difference. If I var_dump(); the query before inserting, the date is ok.

View Replies !