MYSQL :: Tire Size Search Query

Memory Size Exhausted Error - Too Much Info Back From Query - What To Do?
Using PHP and MYSQL I changed one of my MySQL search queries to allow a
situation where the user gets much more information returned then
previously

However, it has now given me the error message:

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to

allocate 42 bytes) in /usr/share/pear/DB/mysql.php on line 290

OK I understand what this means (I assume) that the program is trying
to
allocate more memory to return my query result but it wont let it.

However, I need this query to return this info, so what can I do?

Size Of Indexes Versus Data File Size
I am adding indexes to a table with about 200,000 records. Every field indexed adds about 2MB to the index file (*.MYI).

The index file is around 8MB and the data file is around 10MB.

Is there some optimum ratio I need to know about?

(I have examined the queries and am indexing field used in WHERE, ORDER BY and GROUP BY clauses.)

Date Search Query Help
I'm trying to filter my search results by date. The field name I'm running this for is final_date and is a DATE field.

I want my results to show records that have sales_reps.final_date within the past 30 days.

Query:

SELECT
sales_reps.sr_id,
sales_reps.name,
sales_reps.job_number,
sales_reps.status,
sales_reps.stage,
UNIX_TIMESTAMP(sales_reps.final_date) as final_date,
UNIX_TIMESTAMP(sales_reps.date_to_shop) as date_to_shop,
shop_orders.community,
users.user_id,
u.fname,
u.lname,
users.builder,
users.division,
ei.shop_result,
ei.good_tape,
sa.shopper_id,
shop_orders.order_id,
UNIX_TIMESTAMP(shop_orders.date) as order_date,
pi.fname as pfname,
pi.lname as plname ,
ei.exit_id
FROM sales_reps
LEFT JOIN shop_orders ON sales_reps.order_id=shop_orders.order_id
LEFT JOIN users ON users.user_id=shop_orders.builder_id
LEFT JOIN report_types ON report_types.type_id=sales_reps.report_type
LEFT JOIN shop_assignments as sa ON sa.sr_id=sales_reps.sr_id
LEFT JOIN exit_interviews as ei ON ei.shop_id=sa.shop_id
LEFT JOIN users as u ON u.user_id=sa.shopper_id
LEFT JOIN users as pi ON pi.user_id=sa.pi_id
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <=UNIX_TIMESTAMP(sales_reps.final_date)
AND (sales_reps.status = 'Completed' OR sales_reps.status = 'Rejected')
ORDER BY users.builder, users.division, community ASC, final_date ASC
Is this:

WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <=UNIX_TIMESTAMP(sales_reps.final_date)
incorrect?

I'm getting dates such as 2007-03-22 and 2007-03-19 in my result set and they shouldn't be there.

Could I get some pointers?

Correct Search Query
Can anyone help me out with this please. I am looking at searching among comma delimited id's stored inside "IDs", trying to figure out what the correct SQL query for that would be. I need to extract the ID where IDs contains the value 7. I know I can't use something like "WHERE IDs LIKE '%7%'" because the result would be inaccurate. So how do I go about it, please? Any directions would be appreciated, thanks!

+------+-----------+
| ID | IDs |
+------+-----------+
| 1 | 3,17,20 |
+------+-----------+
| 2 | 1,7,9,12 |
+------+-----------+

Search Database Query
I am looking for a query that will search all of my database without having to use all the individual table names and fields as there are 6 tables and over 30 fields in the database is there any special expression for this the search criteria would only be one word.

Mysql Search Query
i would like to write a query to seach for the related information to display.

let say the user key in the word "sql", and in one of my keyword field i have data such as "mysql, database, java" and i want to call this row out, how can i do this?

Search Query By Date
I'm trying to adjust an older search query to perform a search by date where the date column is an INT field (unix date stamp).

SELECT * FROM incidents WHERE date < DATE_ADD (CURDATE(), INTERVAL $searchDate DAY);

Where date, formally a DATE field, is now an INT(11) field. $searchDate is an int, indicating how many days back to search. 1 (day), 5 (days), etc.I'm assuming mysql's DATE_ADD function is specific to the DATE field. How can I achieve the same result, but with an INT field?

Query Search Direction
It's possible to tell mysql to start searching rows from highers indexes to lowers?

MySQL Search Query
I have two tables as follows:

table 'question' with fields 'patient_id', 'answer_id' and 'terms', and

table 'answer' with fields 'answer_id' and 'answer_path'.

I need to search question.terms for a keyword match and then join that match to the corresponding foreign key (answer_id) in table answer to return 'answer_path'.

[PHP, MySQL, FLASH] - Search Query
just a quick question what would be the best way to search a database, how should i query it? i have this so far:

<?
$name=$_GET['name'];
$company=$_GET['company'];
$address=$_GET['address'];
$phone=$_GET['phone'];
$email=$_GET['email'];
$web=$_GET['web'];

mysql_pconnect("host","random","random") or die ("didn't connect to mysql");
mysql_select_db("database") or die ("no database");

$query = "SELECT * FROM table_name WHERE Name, Company = '$name' AND '$company'";
$result = mysql_query( $query ) or die ("didn't query");

$num = mysql_num_rows( $result );
//echo ($num);
if ($num == 1){
while ($line=mysql_fetch_array($result)) {
$profile = "name_show=" . $line['Name'];
$profile = "company_show=" . $line['Company'];
//$profile = "address_show=" . $line['Address'];
//$profile = "phone_show=" . $line['Phone'];
//$profile = "email_show=" . $line['Email'];
//$profile = "web_show=" . $line['Web'];

}
print $profile;
} else {
print "error=Sorry, but I can't show results";
}
?>

it works but not very dynamically, when both companys are commented out in the while and query statements.

What i hope to have is a search where users can type in 1 letter and it will give them a result with all records having that letter in them and just by filling in one item in flash instead of all of them in order to begin the search.

Incorporate OR Operator In Search Query
I have 2 tables, one with restaurants and one with US centroids, latitudes longitudes, zipcodes for varius central cities in the us.

I have a query that I got working, that uses these 2 tables, it selects all restaurant data from table Restaurants city/state/zip... where the restaurant zipcodes are within the dynamic radius specified of the zipcode(centroid) specified.

This works fine, It also returns the distance as well.

I would also like to select restaurants records regardless if the the distance between zipcodes only IF the Restaurant City matched the CIty I pass to the query. I tried adding an OR operator but my query hung.

Here is my query:

Query A Thesaurus For Search Terms?
I was wondering what the best way is to expand on user-inputted search terms.

For instance, when conducting a search, I would like to ALSO query all synonyms and related words to the inputted search terms.

The database my script searches is very large, so inputting my own related words and synonyms alongside every searched value in the database would be a real pain in the neck if I had to go that route.

Is there some sort of downloadable database of all words and their relations I could use?

Query Search In Different Table Columns
I'm trying to set up an sql-query to find a value in the table, which can exist in different columns......

Query To Search Multiple Fields
I have a table with over 50 columns that contain yes/no values. I want to know if there is a way to write a query to get all fields that have a value of "no".

SQL Query Problems (for Use With A Sort Of Live Search)
I created some code that loaded information from an XML file, but many thanks to jimfraser on here I'm working on a solution that'll drag it directly from the database.

Problem is, it's not displaying anything at all, so I assume there's a problem with the SQL query or the way it's being presented on the page.
PHP
<?php

$network = new COM("WScript.Network");
include_once('../../../functions/datalib.php');

$db = new oracleClass();
$conn = $db->connect();

//get the q parameter from URL
$q=$_GET["q"];

//lookup all links from the xml file if length of q>0
if (strlen($q) > 0)
{
$hint="";

// connect to the database
$sqlstatement = 'select * from TEST."WFO_REPORTS_ALLEMPS" order by "Surname", "Forenames"'
$sqlListings = OCI_Parse($conn, $sqlstatement);

OCI_Execute($sqlListings);
while(OCI_Fetch($sqlListings)) {

$name = oci_result($sqlListings, "Forenames") . ($sqlListings, "Surname")

//find a link matching the search text
if (strchr($name,$q))
{
if ($hint != "") {
$hint .= "<br />";
}

$hint .= "<a href='#' onclick='employee_form.Line_Manager_Staff_No.value="
. oci_result($sqlListings, "Gc_Staff_Number")
. ";checknumber('Line_Manager_Staff_No','displayusername');'>"
. oci_result($sqlListings, "Surname") . ", " . oci_result($sqlListings, "Forenames")
. "</a>";
}
}
}

// Set output to "no suggestion" if no hint were found
if ($hint == "")
{
$response="No user found.";
}
else
{
$response=$hint;
}

//output the response
echo $response;
?>

On a side note, is there a way I can change it so that the search will present any users based on their first name, as well as the surname?

Mysql Query Search And Find In String
I have following values in a field of mysql table. These values are stored as string [varchar] in the field.

Values : 3,4,10,21,20,8,100,2,6

How can I check through MYSQL query that 100 or 21 or 4 exists in the string?

Query To Search And Retrieve File For Download
I am new to web development and have this challenge. I want to design a dynamic website for a college community using PHP and MYSQL. The objective of the site is to hold the annual report of all the departments such that visitors can select the desired department and the year of the report. The years could stretch back to as long as the college existed but I want to limit the backdate to 10 years for now. The visitor should be able to select a department and desired year, view the report online and also be able to download the same report stored in a directory in ms word or PDF format. What should the table structure look like? Also the PHP and SQL script to search for the stored files and make them available for download.

SELECT Search Query - Table Join Required? Help Please!! (PHP + MySQL)
I have a search form that has:
- drop down with states (nsw, vic etc)
- drop down with all business categories (retail, commercial etc)
- keyword / postcode field (2000, or 'builders')

The user gets results returned from the business table filtered by state (mandatory), which category is selected (mandatory) and by keyword (optional) or postcode (optional).

If keyword / search phrase is given then it will do search of the keywords fields of the business table (has already been indexed) in the selected category only. If postcode it will return all businesses in that category in order of distance from the given postcode.

I have 5 tables (additional fields ommitted):

'state'
state_id, name

'businesses'
business_id, keywords, name, postcode, state

'postcodes'
fromPostcode, toPostcode, distanceKMS

'categories'
category_id, name

'business2category'
business_id, category_id

Please dont thing i'm just pawning off my work here!!
Basically, i've got this working already, but only just, and in a very long and convulted format. Its far too long to post here, but since i'm not fully versed in table joins, i've been searching individual tables (e.g. SELECT *,MATCH AGAINST etc), building arrays, searching arrays again, and then building results at the end to fit into the paginator. Now the search is taking too long to perform, and i need a leaner alternative. not to mention theres way too many lines of code, and i just know theres a better way.

There must be a very simple way to achieve the following searches using table joins, can anybody please help me with 3 search examples below so i can try to understand joins better?

Search 1:
State + Category only

Search 2:
State + Category + Keyword

Search 2:
State + Category + Postcode

If there is no postcode, the others still need to display the data
filtered by distance from a default postcode of 2000.

I'd really appreciate if anybody has a few minutes free to help out here, and hopefully teach me something about effective table joins and searching. I've omitted the extra fields and tables from the real structure, and just left the relevant ones above - if theres anyhing missing or not making sense please let me know and i'll fix up asap.

Search: Remove Quotes From Database For Search
I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike". I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

But i was wonderind is there any way i can search for "Peters" and "Johns".

Size
What is the Size limit to MySQL can it have 200 gigs of data?

DB Size
i have a problem with my db Import maxsize (2.048KB) and i want to make it 12.000KB. How can i do it.

I Have A Search Form To Search Through The DB.
I have a DB with 50,000 entries. I have a search form to search through the DB. If the recno=30123456 and you search the recno, you get that one file. what if i want a query that searches alll recno's that contain 30?? Can I do that using mysql or do I need to program that in my java app?

Can I Find Out The Size Of My DB?
I am looking to find out the size of my database. Is their a command I can simply write to receive this?

Table Size
I know this is probably a dumb question and mostly because i'm too lazy to research it. But if anybody knows off the top of their head it will be muchly appreciated.

How big can a mysql table be while running on an NTFS partition such as Win 2000 server or XP?

I know the max table size in mysql is 4 terabytes but what is the maximum file size on NTFS.

Blob Size
I have a question about the size of a blob if it indeed needs to be set
and cannot be variable. I ask this question because I am trying to save
web pages to a database but a web page can be anywhere from 10
characters to 200,000 characters and using blob(200000) is very
inefficient.
I was thinking of using various size tables ready for various size
pages but I would be easier to manage having them all in one table.

What Is The Max Size Allow For Mysql???
when I dump my db (as in mysqldump) I realize that the size of file is getting big (I guess this is one of the database's nature - to grow :-) ).

Right now the size of my db is 200Mb and my estimation that it will grow in 800Mb/Per year (so it'll reach 1Gb by the end of the year)

Question: what size would it be wrong to use mySql? I mean, if my db size is 3Gb am I ok? should I expect performance issues? or it doesn't matter.

Max Field Size
I have an Access 2000 database and the max field size for memo data type is 65000 characters I think. I am thinking of switching to MySql.

Does MySql have a data type field that is larger and if so what is the maximum size it can be?

I would like to use a database where I can store achive info and was thinking a MySql field can handle a large amount of data?

Size Of A Record?
How to find:

1. total size of data in bytes, in a record
2. total size in bytes of a record (including MySQL's own header bytes)

Database Size
Is 25mb (database) enough to handle 1.2 million records in one single table with 7 columns. What is the right Size of the database in order to handler that much records/rows?

Key File Size
We have loaded a very large dataset and received an error message stating that the key
file is full. How do you go about increasing the allowable size of the key file? I assume by the key file the error message is referring to the .MYI file. Is this correct?

Mysql Size
Could anyone show me how to get the size of a database together with how to set the database size when create a new db?? In SQL server, it's pretty easy but it seems complicated in Mysql.

Size Of Database
I want to know the size of my vowkrs database.

I am trying to run the command

function getDbSize(vworks){
$size=shell_exec("du -b /var/lib/mysql/vworks");
$size=explode(' ',$size);
return $size[0];
}

but am unsure if it is correct and where to run it from?

Bin Log File Size
My bin log files get huge and the only time it creates a new one is when the server restarts. Is there a way to make it create a new bin file every week or when it reaches a certain size?

Size Of MySQL Row With PHP
Is there any way in php to display the size of a row from a table in Kb? Cant find anything online, but i'm assuming it can be done.

Problem Getting Row Size
I'm working on an application which clients will use to store product information to the database. Because I'm constraining each client to certain space usage, I need to use a functionality that will return the size (in bytes) or data entered per row by each user and store it in a column of same table which I'd probable call size.

Max Size Of A Database?
What is the maximum size of a single database file in mysql? What is the maximum number of record can i have in a single database? what is max number of field can i have in a single record. Which is the best version of mysql 4 or 5?

The Size Of MySQL
I've been thinking of using MySQL but after downloading it to test it out I see that its over 25MB in size. Why is it so large? (Of course relatively speaking.) If I dont need *anything* fancy, just simple storing and retrieving of information, why would I need such a large type of database?

Blob Size
How i can get the blob size field in bytes?

Database Size
There is a general board-listing table. All boards in the forum are stored in there. For every board created, a MySQL-table is created and it's name stored in the general board table.

The table that is created along with the board, contains the data for the topics, as well as a reference to the appropriate topic table.

. The topic table contains the posts of the topic.

This all means that there will be a seperate mysql table for every board and for every topic.

Now this means that there will be a lot of tables because there will be a lot of postings and topics.

Therefore my question is: is it a problem, or sort of slowing MySQL down if there is a lot of tables in a database?

Calculate Row Size
SHOW TABLE STATUS FROM your_db_name [like 'tabe_name']

Will give me average row sizes, but let's say I needed an exact size for each row. What would be the method to select the size of a specific row?

Innodb Max Size
I need innodb becasue of acid.

I have a database which I am trying to import and once the ibdata1 hits 4096Mb I get an error file full.

Most of the data is images (that is why the size). My question is this: can this file grow larger then 4GB. I cannot see this limit in the my.ini file. If not how do I store more that 4gb ofdata in innodb.

Regarding Index Size
I am fairly new to MySQL and dedicated databases in general, and just had a quick question in relation to index sizes.

I've got a InnoDB table with 105,000 rows, each with about 140 bytes of data. I have an index on one INTEGER (4 byte) field, set up as a BTREE index. All good so far. Problem is, MySQL Administrator is stating that the size of the index is 6.5MB, which would be about 62 bytes per index entry.

I guess my question is, why does MySQL need to store 62 bytes of information on a 4 byte field? Obivously you would have to store the 4 bytes in the binary tree, as well as some pointer to the physical location of that row on the disk (say another 4 bytes), giving you a total of 8. This is a lot smaller than 62.

It's not really a problem at the moment, but the table in question is expected to grow to ~ 60million records, giving an expected index size of 3.72gb. Ideally I want the index stored in memory, and 3.72gb is a fair bit of RAM to be used to index one integer field on one moderately sized table.

I'm thinking perhaps I'm doing something wrong, or there's something I'm missing.

Max Table Size
Basically whats the maximum table size in GB that MySQL allows? We are
developing an application that streams content - The streams are created
by a CMS from a raw file that gets uploaded by a user - These raw files
are stored in the db by the app. In Oracle i'm used to creating mutiple
datafiles that make up a tablespace and so no singe data file ever
really gets that big. Is the same approach applicable in MySQL and if so
how big can you go?

Database Size
I have tried deleting hundreds of records from several tables in my MySQL database and I have found that although the number of records in these tables has decreased, the size in Mb has remained the same.

I checked the data directory and the .MYI files seem to have been amended but the .MYD files have remained the same size.

Is the data still there? I really want to remove it both for security and to keep file sizes down.

Exceeding The Size
exceeding the maximum size of a TEXT data type. Should I be worried and use something like a MEDIUMTEXT or LONGTEXT?

The project background is that it is a project that aims to give others ability to create projects and manage them. Kinda vague, but whatever.

So should I have any worries about exceeding the variable size of a TEXT (normal) and use something like MEDIUMTEXT or LONGTEXT data types?

And what effect (other than bigger variables) will MEDIUMTEXT and LONGTEXT have on my server?