MYSQL :: Search Over 1 Million Records (7 Columns)

615 Million Records
my record count is up to 615 million. In 3 months it will be over 1.2 billion.
Problem: My webpage takes over an hour to display records.
(Using php)
1) I know my table design is very good.
2) Using the admin tool I see that my query takes 17 seconds
Then it reports that MySql is still doing something after the query is complete.
Does it buffer the data? (lots of records)
Does it write to a temp table?
Why is it taking so long?
(My query is super fast in spite of the large number of records.)
Can anyonme provide insight on what mysql does post query?
How about tips on increasing performance for large databases?

Slow Queries, 4 Million Records, Need Educated Advice!
I have created an app a few years back to store some records in a DB.
According our calculations we were never to exceed 500,000 records in
the DB. Seems we were off by a decimal point or so.

I set up a FreeBSD box with MySQL three years back and it has been
filling up. One table has over 4,000,000 records. Yes, four million.

As a web developer and not a DBA, I have struggled to upkeep the server
the best I can. As of the last one million records the server has been
struggling to keep up with multiple requests and as you can imagine the
user base is growing too.

Hardware:
Dual Xeon 3.06 Ghz
4 GB ECC RAM
800GB RAID5 SATA array

Software:
FreeBSD 5.3
Apache 2.0
PHP5
MySQL 5.0.2

Basically I have to perform a search on one of two columns in this huge
table (10 columns, 4 million rows).

The table is MyISAM with a single primary key that is used largely for
updating row data.

Most records are ten digit numbers for one column and a ten digit
varchar for the other, but sometimes either column can be a series of
characters up to 100 chars long, so each column is set for
varchar(100).

The action performed is

SELECT count(*) FROM tableName WHERE col LIKE "%123%"

then...

SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25

with "%123%" being any random string typed into a search window.

Search Multiple Columns
I would like to search for a keyword '%schultze%', and I would like the search to include more than one column. I was experimenting with this:

SELECT * FROM `listItem` WHERE `owner` = 128 AND MATCH (`title`) AGAINST ('%schultze%');

But I would get funny errors complaining about not being able to find a FULLTEXT index. I imagine this is something that has to be built before the search happens. As the database is changing all the time, I would want this to be built once a day. Perhaps I could stick a batch file in the xinetd that would take care of that.

Ideally I would like to search over 10 columns, (with the constraint that the owner equals 128) that are varchar and text, all utf8_general_ci. I suppose this would look something like:

SELECT * FROM `listItem` WHERE `owner` = 128 AND MATCH (`referenceNumber`, `title`, `menus`, `added`, `format`, `ratio`, `source`, `description`, `genre`, `rating`) AGAINST ('%schultze%');

Or perhaps I'm barking up the wrong tree here and I could do a search that was like this:

SELECT * FROM `listItem` WHERE `owner` = 128 AND (`referenceNumber`, `title`, `menus`, `added`, `format`, `ratio`, `source`, `description`, `genre`, `rating`) LIKE ('%schultze%');

But where mysql would know each column was OR, where '%schultze%' matches for `referenceNumber`OR `title` and so on,

How To Text Search Across All Columns
Can some tell me of a syntax to search for a text string that might be
in any column in a table. Something simular to:

SELECT * FROM mycontact WHERE * LIKE 'string';

Search Two Columns In One Table
Is this right?

SELECT *
tablename
WHERE column1 = 'value'
OR coulumn2 = 'value' .

Search Columns For Match
I need a query syntax or command line to search all tables for a column name like 'whatever%';
I needing to search all tables in a database for column names that match without listing each table name, a global table search.

Search On Wildcard Columns
I have a query such as:

SELECT * FROM sr_main WHERE originator LIKE '%pcb%';

What I would like to be able to do is have a query where my column type is not fixed - in the example I would swap my distinct column name 'originator' with a wildcard such as '*'.

Is it possible to return all the results of a search of all fieldnames with a LIKE rather than an '='? .

Multiple Records As Columns
I have a table of Sessions and a table of Speakers. Each Session could have several Speakers and each Speaker could speak on several Sessions. I have a link table containing just SessionID's and SpeakerID's. All this is working fine on a successful website.

I am now trying to write a query that will return records for each Session with each of the Speakers' details for that Session. Each record could have a different number of fields according to how many Speakers were assigned to that Session - this is the bit that makes it seem difficult.

Search Results In Multiple Columns
Currently using

WHERE email LIKE '%$keyword%'
I would like for the keyword to look into multiple fields

WHERE email,fname,lname LIKE '%$keyword%'
Searching the entire table would be fine too.

I tried something similar to the above and it didn't work. Sorry if this has been covered before. I couldn't find a thread...

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......

Search Multiple Columns And Return One Set Of Results
I have a table that has three fields that I want to be able to search (county, town, village). The problem being that I then want to be able to return a single set of results in Alpha order.

For example, if I write three queries that each search one of the three fields for LIKE 'P%' to give me all records that start with the letter P, I then need to sort the combined results into a single list in ascending order.

I can write three queries but end up with the data in different fields (obviously) and can not find a way to merge the field values into a single column. Merging the 3 fields for each value will not give me what I want, I need to just see the value for the field that satisfies the select condition.

Full Text Index And Full Text Search On Several Columns And Indivdiual Columns
1. Say if I want to do the full text search on three columns, "title", "description", "keyword". The search could be done only on one column, or combine any two columns, or combine all three columns, do I need to create full text index for all these possiblilities, like full text index on each column, and then full text index on any two columns and then full text index on all three columns?

If I have to make so many different kind full text index, that will slow my database performance or not?

2. Will the relevance also be calculated based on which column, such as the "title" will have higher relevance than "keyword", the "keyword" will have higher relevance than "description"?

Search Only Some Of The Records
I am searching a database with around 820,000 records with about 40 fields. I am only searching through it by two fields though. The data in the table goes back a ways (around 5 years) but I am only concerned with searching through the last 100000 records or so. Here is my query:

select home_id from home_data where street_number like 'street_number%' and street like 'street%'

is there a way to tell it to only look at the last 100000 records and not the whole database?The query takes forever and I would like to speed it up.

How To Search Records With Starting Letter From 0-9
How can i write a query so that i can get records that starts between 0-9.

For example i have 10 records like

apple
2apple
ball
9ball
cat
4cat
6cat

I need a query to get results that starts with numeric letter.

My out put should be like

2apple
9ball
4cat
6cat

A Million Rows
i'm setting up a squid log analyser.
i wrote a little perl script which copies the log file entrys into a mysql db (with only one table).
i get ~5 million entrys a day. so my table gets larger day by day.
all entrys older than 30 days are automatically deleted.
now i'm searching for a method to collect the data from the sql table to generate a report (perhaps a online report via php).
but it's very slow to collect that data (atm ~30 million rows).

i tried to collect the amount of connections the last 24h data with the following command:

$ToDate = time() - (24 * 60 * 60);

print " <tr>
<td align="left" class="row1"><b>Total Connections</b></td>
";
$result = SqlSelectQuery("SELECT DISTINCT id FROM logfile WHERE time > "" . $ToDate . """, __FILE__, __LINE__);
$number = SqlNumRows($result);
print " <td align="left" class="row1">  " . $number . "</td>
";

well it works, but it takes a while and as result i get "1629258".
so about 1 minute to collect that data? ~1.5 million rows?

100 Million Record
I would like to store 100 million record using MYSql. Can MySql store 100Million record?

Float A Million
I have a problem making the price: 1.200.000
Until now I was using only prices under one million so it was no problem using:
MySQL Table Syntax: `koopprijs` float(10,3) default '0.000',

Cross Table Search For Orphaned Records
I have two tables in a databases. The tables are:

orders
devices

Orders table has a primary key order_id which auto-incs. Devices points to a single order via a field, associated_order_id which is an int.

I am trying to execute a query to return all devices that have an associated_order_id which doesnt acutally exist as a valid order_id in orders. I.E. Orphaned devices with no parent to an order.

Here is what I was thinking, but IT DOES NOT WORK. It returns thousands upon thousands of results.

SELECT devices.* FROM devices, orders WHERE devices.associated_order_id != orders.order_id

How-to Calculate The Size Of 1 Million Rows?
Anyone have any tips for calculating the size of a certain number of rows in a MySQL DB? If phpMyAdmin gives my row size as 19 bytes will 1 million rows be 1 million times that size?

2-3 Million Record Table With MySQL
Does anyone have any experience working with very large tables? Say, 2-3 million records? I have the opportunity to work on a new project where at least one of the tables could grow to be that size, and I'm looking for any input on if MySQL is a good solution.

Logical OR On Columns? (Merging Two Or More Columns Into One Column)
Considering the following example table:

Column1 | Column2 | Column3
===========================
NULL |"Value1" | NULL
---------------------------
"Value2"| NULL | NULL
---------------------------
NULL | NULL |"Value3"

In each row only one column contains an actual value (a string in our
example), the other columns are set to NULL.

I would like to merge those columns into one single column, discarding
the NULL entries.

The desired result would look like this:

Merged_Column
=============
"Value1"
-------------
"Value2"
-------------
"Value3"

A '"SELECT column1 OR column2 OR column3 as "Merged_Column"' doesn't
work unfortunately. Is there an easy way to accomplish this on the SQL
Server?

The next problem I am going to face is that every column is in fact
made of a different data type (ie. Date, Int). Is there a way to
modify the merge select (if that's possible) to have it cast
everything into a single specific type? Ie. make everything in the
merged column a string.

How Do I SELECT FROM Columns Where The COLUMNS Has A PATTERN?
I've been looking far and wide on this one. I'm looking for a way to select from columns where the columns have a certain pattern!

Example:

Instead of doing:

SELECT q1, q2, q3, q4, q5, q6, q7, q8, q9 FROM exam

I would like to do:

SELECT q* FROM exam

or

SELECT q% FROM exam

Whatever will match everything that has the letter q in it. Anyone have an idea? I've been banging my head on this one.

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".

Count Related Records, But Show Records With NO Related Records Also
cl_items
========
it_id (pk)
it_ownerid
it_name

cl_offers
========
off_id (pk)
off_itid (fk) -> to it_id
off_whoid

My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers)

I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also.

Is there any way to do this with mysql only?

All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`

Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?

Delete Records Without Matching Records
I have a one to many relationship in my database. I would like to delete records in one table if there are no matching records in another table.

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?

Converting From Many Columns/few Rows To Few Columns/many Rows
So I have a table that looks like:

Code:

char_id1234(...up to 30)
582NULL416
25739NULL12
391NULLNULLNULL

Each char_id is the primary key in another table, each numerical column value is the primary key (skill_id) in a third table, and each data value is the primay key (rank_id) in a fourth table.

I am attempting to join all four tables into a single query, but it seems impossible without converting the table to something like this:

Code:

char_idskil_idrank_id
5812
5834
58416
2517
25239
25412

How Many Columns Is Too Many?
I want to create a mysql table for a project that I'm working on.

The table will have about 300 fields.

Do you think this is too many?

Will this cause any performance problems?

Best Use Of Columns
I have a table which holds user data and im at the design stage.

My question i would appreciate advice on is as following -

In theory is it better to have

A) Table with 200 columns with smallint datatype
B) Table with 100 columns with varchar(50) datatype
C) A combination of the above

Both will work fine but which do you reckon would be best?

Once the data is entered it will only ever be retrieved by use of a primary key.

Whole Row Vs. Some Columns Only...
What is the performance difference between selecting a whole row and only selecting a few columns? Is there significant performance difference, if a lot of the columns are NULL any way?

How Many Columns?
I have no idea what the performance will be if there are frequentselect/replace operations on this table(more than 200 columns which are almost int(11) type).

If too many columns delays the query, how many is appropriete?

Add More Columns
I need to store information in the DB, but won't need it for every row in the table all of the time, so some of the cells will be empty most of the time.
Would it be more efficient to make a new table?
It's probably 2 or 3 columns worth of data and the original table can have several dozen rows.

Two Columns
I'm having trouble getting this right. Right now as is I have a table where the only relevant column is g_id.

g_id int(8) NOT NULL,
UNIQUE KEY g_id (g_id)

What I'm trying to do is add another column, for example g_username, so that there can be multiple rows with the same g_id, multiple rows with the same g_username, but no two rows can have the same g_id AND same g_userid. I don't really know what flags to add to the columns to make this work. Obviously g_id cannot remain unique, but beyond that what do I need to do?

Columns
im developing, what i consider to be, an extremely
large database, using mysql and php.
im concerned that one of my sql tables will be too large.
tonight, im running some tests, but i was hoping the forums
could toss me some good advise.

the table will be around 1500 columns long, and
about 500 rows on average.
each column will not be any larger than 20 varchars.

i've never created anything beyond 30 columns.
is this beyond what's recommended with using mysql ???
i know the queries may take a little bit to load, but is
this simply a bad idea anyway, or am i just getting nervous
for no reason at all.

Columns
I'm totally new to databases and I have no idea what I'm doing. I've made some websites before and I have a domain name and ftp access to it. Now the really hard part (or for me atleast). How would I make a database on it? Even something very simple would be nice. Do I need to install something on the server? What do I install on my machine to edit it? Please, even if you think its obvious and not needed to be said, do anyway. I'm lost and anything will help me at this point.
I want to have database that would have the following columns (so maybe you get the idea?)

Username
Password
Choice1
Choice2
Choice3
..etc

Sum Of Columns
I have a table called app_created which contains information of applicaton created in present and past.

e.g. table app_created is having following columns:
create_today,create_yesterday,create_2daysago,create_3daysago,
create_past1week,create_past2week,create_past3week,create_past1month

i am able to get records for these exact durations but now ,in app created in past 1 week ,2 week,1month...i ahve to put sum of

created_today+create_yesterday+create_2daysago+create_3daysago = create_1weekago .

i don't want to write cursor or procs for this i have to do this in single query.

Searching Across Two Columns
I'm trying to search two columns - phone_brand and phone_model.

I'm using the following query of which will successfully match something like Nokia or 6133, but not Nokia 6133. I realise this is because the wildcards will only match either side (or one side in the case of the brand column), so I was wondering, what would be the best way to match the whole string?

$sql = "SELECT col1,col2 FROM mytable WHERE (phone_brand LIKE '".$var_keywords."%') OR (phone_model LIKE '%".$var_keywords."%') AND status=1";

Too Many Columns: Array?
I'm trying to convert a system based on text files to MySQL. It currently uses the following two files for user registration:

A text file that holds name, email, password, etc.
A text file that holds the email and approximately 100 other values. These values each range from -100 to 100.

I want each text file to be a separate table, but I don't know how to best implement the second table. Is it bad to have so many columns? How would I type it out without writing CREATE TABLE vals (..., val40 tinyint, val41 tinyint, ...)? Is there anything like an array in MySQL?

Is 68 Table Columns Too Much?
I have just gone through a questionaire and converted the questions into tables and columns.

Despite having 8 additional lookup tables (for many-to-many associations), I'm still left with a main customer table with 68 columns.

My gut tells me this is too many, but what do you think?
Whats the sensible maximum amount of columns for MySQL, with performance in mind?

Merge Columns
I want to merge three of my text columns in my table into one text column. Currently the three columns hold content with html. Is there a way to merge the three columns into one without losing any content?

Sorting By Two Columns?
Is it possible to ORDER BY one column ASC and then by another within the first order?

You know like when you do a data sort in excel, you enter your first column and then your "then by" column and that second sort only takes place within the first?

PS: I tried using the search function for this, but was unable to find anything.

Amount Of Columns
Does the amount of columns in a table matter? My member table has around 30 different columns and i'm wondering whether this is too much.

Group By And Other Columns
I have an SQL statement such as the following.

"select sum(transaction_amount), file_number, party_code, t_date from table group by file_number"

The file_number can be same for many rows but party_code will be different for each row. How can I get all possible values of party_code field. Currently I get only the party_code for first record.

Combining Columns AS
I am trying to look at 3 columns in my table to see if they have values, then combine them as a new value to appear in an alphabetically listed dropdown menu. here is the code i am using:

SELECT Department, Department2, Department3 AS Dept
FROM Directory
ORDER BY Dept, Lastname, Firstname

this however is only returning rows that have values in ALL 3 columns.

Copying Columns
UPDATE newusertable B
SET B.username = A.username
WHERE B.userid = A.userid
FROM oldusertable A

But it's not happening....any ideas? I've looked at insert ..into and create
etc but nothing seems to provide the cross-table check i need to perform to
ensure that the right entry is updated to the right record.

Count Columns From 1 Row
Ok, I don't know if this can be done in SQL, but would have thought so.

I have a table with columns:

id1__id2__id3__id4__id5

I would like to count up the occurances of each Id, for one row...e.g

id1__id2__id3__id4__id5
1 ___1 ___1 ___2 ___4

gives
id___count
1____3
2____1
4____1

Is there a way to do it, without changing the way the table is structured? (count(*) won't work because it is all in one row)

COMMENTS In Columns
The MySQL SQL syntax allows COMMENTS to be added while creating columns and tables.
In the manner of Access, one could expect to retreive these comments in the REMARKS
field when calling SQLColumns() in the ODBC driver.

However, this field alwas comes back empty, whatever comments were given when the columns
were created.

MIN() Over Multiple Columns
I need to find out how to find the minimum value usig a query over three columns:

SterlingTradeGross
BlowOutNett
SterlingTradeQGross

These are the three columns, I need a query which calculates the minimum value from all of these, how do I do it?