MYSQL :: Optimizing Search Query For Millions Of Rows

Optimizing Search Count & Order By
Basic searches run for about a second or so, and searches within a range of dates runs for about 3 seconds on average. Neither is great but it suffices for now.

Unfortunately, to get it in that time, we always have to limit our result set to only what is needed for the page (~20), so we can never get an actual count on the full result set. Trying a simple COUNT(*) on the search query before pulling only the 20 needed takes forever. Also, any Order By on the date takes forever.

Our search table structure is below:
articleID int(11)
id varchar(25)
issuedate date
subhead varchar(150)
title varchar(254)
abstract text
titlename varchar(100)
alphadate varchar(60)
pageno varchar(10)
summary text
countId bigint(21)
category set('a', 'p', 'b')
three_in_one tinyint(1)

I just started working for this company in June, and I thought of converting the issuedate from YYYY-MM-DD to a unix timestamp, removing alphadate and possibly id & summary, and normalizing titlename (which is the article's publication), but I wasn't sure if this would help any of the search queries. The table is at 1.33 gb with a full-text index taking 350 kb of that.

Optimizing A Query
I want to optimize a very expensive query, the query joins 5 tables, each having tens of thousands of records, always returns about 15000 matches and performs several calculations on each and every records (a datadiff, five time the / operator and two * operators), and to end with, all records (probably about 45000) are grouped and sorted resulting in 15000 records. This takes about 80 seconds, I want to get it down to max. 2 seconds :-D
All indeces are made, so I won't be able to gain anything there anymore. Does anybody has a clue as to how I could proceed here (maybe something with caching, materialized views and triggers?).

Optimizing Another Query
I have another Query, that I don't manage to optimize. It's hard. I tried to first readout the ID's and then readout the contents, but this is not faster ...

Here is the Query:

SELECT o.id AS oid, o.time AS otime, o.disNaviReferer AS showR,
o.disNaviToplist AS showT, o.disNaviPartner AS showP, o.description AS odesc,
o.type, o.used, o.html,
c.id AS cid, c.name AS catname,
e.id AS eid, e.beschreibung AS linkdesc, e.flag, e.freigeschaltet AS etime,
e.previewPic as prev, e.hits
FROM own_content o
LEFT JOIN link_entries e
ON e.link = o.id
LEFT JOIN link_cats c
ON c.id = e.cat_id
WHERE o.used = 1
AND type = 4
ORDER BY
etime DESC
LIMIT 0, 30
Here ist the table structure:

--
-- Tabellenstruktur fÃ¼r Tabelle `link_cats`
--

DROP TABLE IF EXISTS `link_cats`;
CREATE TABLE `link_cats` (
`id` tinyint(2) unsigned NOT NULL auto_increment,
`name` varchar(100) collate latin1_general_ci NOT NULL default '',
`cflag` tinyint(2) NOT NULL default Ƈ',
PRIMARY KEY (`id`),
KEY `flag` (`cflag`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃ¼r Tabelle `link_entries`
--

DROP TABLE IF EXISTS `link_entries`;
CREATE TABLE `link_entries` (
`id` smallint(11) unsigned NOT NULL auto_increment,
`flag` tinyint(2) NOT NULL default '-1',
`eingetragen` int(10) unsigned NOT NULL default Ɔ',
`freigeschaltet` int(10) unsigned NOT NULL default Ɔ',
`cat_id` tinyint(2) unsigned NOT NULL default Ɔ',
`beschreibung` varchar(100) collate latin1_general_ci NOT NULL default '',
`link` varchar(255) collate latin1_general_ci NOT NULL default '',
`extern` tinyint(2) NOT NULL default Ƈ',
`previewPic` varchar(255) collate latin1_general_ci NOT NULL default '',
`hits` int(11) NOT NULL default Ɔ',
`voteAnzahl` smallint(10) unsigned NOT NULL default Ɔ',
`VoteSumme` mediumint(10) unsigned NOT NULL default Ɔ',
`poster` varchar(100) collate latin1_general_ci NOT NULL default '',
`poster_hp` varchar(100) collate latin1_general_ci NOT NULL default '',
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
`mail` varchar(100) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`id`),
KEY `cat_id` (`cat_id`),
KEY `flag` (`flag`),
KEY `link` (`link`),
KEY `freigeschaltet` (`freigeschaltet`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6242 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃ¼r Tabelle `own_content`
--

DROP TABLE IF EXISTS `own_content`;
CREATE TABLE `own_content` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`html` text collate latin1_general_ci NOT NULL,
`description` varchar(255) collate latin1_general_ci NOT NULL default '',
`time` int(10) unsigned NOT NULL default Ɔ',
`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
`used` tinyint(3) NOT NULL default Ɔ',
`last_used` int(10) unsigned NOT NULL default Ɔ',
`used_by_fl_id` smallint(5) unsigned NOT NULL default Ɔ',
`type` tinyint(2) NOT NULL default Ƈ',
`disNaviReferer` tinyint(2) NOT NULL default Ƈ',
`disNaviToplist` tinyint(2) NOT NULL default Ƈ',
`disNaviPartner` tinyint(2) NOT NULL default Ƈ',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=299 ;
Thank you very much! This query is very important for me to be optimized, because I execute it quite often with diferent data ...

Optimizing A SELECT Query
SELECT id, gameid, name, type FROM media ORDER BY date DESC LIMIT 5
SELECT * FROM games ORDER by wishlist DESC LIMIT 5'
SELECT * FROM games ORDER by mygames DESC LIMIT 5
SELECT mediaid, count(*) AS number FROM download WHERE date > &#55614;&#57159;-08-14 10:44:43' GROUP BY mediaid ORDER by number DESC LIMIT 5
SELECT * FROM news ORDER BY id DESC LIMIT 14'
SELECT gameid, count(*) AS number FROM views GROUP BY gameid ORDER by number DESC LIMIT 10
All of these queries I have determined are inefficient (using some tools) and they all have the same thing in common. They're all trying to find the "Top 5" or "Newest 10" or whatever from a table, but in doing so they are going through every single value and comparing them.

What other way is there to do this same idea of getting the "top 5" of a certain column values, without the SQL having to check every entry?

Help Optimizing UPDATE Query
I have 1 query in my script that is taking waaaaay too much time.

Is there a way that I can speed up or optimize this query?

Right now the query is taking about 600+ seconds to update 500 rows.

The Query

PHP

$update_from_sas_postmeta = "UPDATE wp_postmeta, sas_postmetaSET wp_postmeta.meta_value = sas_postmeta.meta_valueWHERE wp_postmeta.post_id = sas_postmeta.post_idAND wp_postmeta.meta_key = sas_postmeta.meta_key";$update_from_sas_postmeta_result = mysql_query($update_from_sas_postmeta) or die("MySQL ERROR (update_from_sas_postmeta_result): ".mysql_error());

The Tables

CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default Ɔ',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=294705 ;

CREATE TABLE `sas_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default Ɔ',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Sample Data
2, 2, 'page_template', 'default'
3, 3, 'merchant_id', ?'
4, 3, 'merchant', 'SomethingSomething'
5, 3, 'link', 'http://www.website.com/link.html'
6, 3, 'thumbnail', 'http://www.website.com/thumbnail.jpg'

More Details
When the UPDATE query begins to run, the sas_postmeta table will have 500 records in it and the wp_postmeta table will have about 250,000 records in it.

Optimizing A SELECT .. WHERE .. LIMIT Query
(using MySQL 5.0)

First, the problem: I need to select different "pages" of IDs that match a small list of categories.

Here's my table:

CREATE TABLE `testTable` (
`id` int(10) unsigned NOT NULL auto_increment,
`category` tinyint(3) unsigned NOT NULL,
`username` char(15) NOT NULL,
PRIMARY KEY (`id`),
KEY `cat` (`category`)
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1
And an EXPLAIN of my query:

EXPLAIN SELECT * FROM myTestable WHERE category IN(5,8,11) LIMIT 5000,20;

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | myTestable | range | cat | cat | 1 | null | 49904 | Using where
So from that we can see it's hitting 50,000 rows to get the 20 rows that I want (it's a table with 1,000,000 rows total).

I've been using MySQL for a fair amount of time, but I haven't ever done much optimization beyond the standard "add an index on it".

Would MySQL Handle Millions Of Records Tables
I already found on the MySQL web site that some users did have good
results with tables of some millions records.

But, what I want to do is store some hundreds millions records in a
table. 190 millions to start and maybe a lot more after. Does someone
already use MySQL with such a quantity of data ?

I was also asking myself how a simple SELECt query like this one below
would perform on such a table.

SELECT * FROM MyTable WHERE MyPrimaryKeyField = Value;

I think that with indexes this shoudl be fast despite the fact that
the table is huge.

An other kind of query i'd like to do is :

SELECT Count(*) FROM MyTable WHERE ABooleanField = True;

I expect this one to take several minutes, maybe hours. Is my guess
right or could it be lot faster/slower ?

Search Through Rows Until You Hit Field With Right Value
Basically, I'm trying to figure out how i can scroll through the rows in a mysql table until I hit a row which has a field containing a certain string. This is a ruff image of how my database looks (ignore the dots...it wouldn't let me leave white space):

id | username | some_field | | some_other_field |

1..|...bill........|...............| |........................|
2..|...fred......|...a value..| |........a value.......|
3..|...ted.......|...............| |........................|
4..|...jeff.......|...............| |........a value......|
5..|...craig.....|...............| |........................|

So, basically, i need to be able to start the query a Fred's 'some_field' then I need it to search on until it finds the next field with 'a value' in...in this case it would be Fred's 'some_other_field', but it could also be any other field in the table.

Full Text Search And 50% Rows
If a full text search returns more the 50% rows, it will return empty sets.

My database is relatively small.

1) How could I know that it returns the empty sets because it has got more than 50% rows, so I can inform the user that he is using a "too common" words, instead of leaving him sitting there wondering why he cannot find anything with a very popular search keyword?

2) Is there anyway I can change the rule, say if it returns 80% or 90% rows, then make it empty sets? Or turn off this 50% rules totally? Just return whatever they find?

Quickly Search Full Rows
I'm building a search thingy and some of my SQL looks like this right now:
Code:

SELECT title,description,link FROM table WHERE title LIKE '%foo%' OR description LIKE '%foo%' or link LIKE '%foo%'

I am thinking it might be faster to use a join or a view or something to search a whole row, but I am fairly new to SQL. How to quickly search full rows?

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

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

Tire Size Search Query
I have a table of tire sizes... example: 245/40 YR18, 245/40 W18
I need to search through that table, and pick out sizes based on a search that would like like this: P245/40ZR18
I want to find tires based on the 'P245/40' and '18'... any combination of letters between the two are fine....
seems like a regular expression seach?

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.

Several Rows From One Query
I've this sql-query...

$sql = "INSERT INTO database (some_kind_of_id, names) VALUES $xxx, $_POST['xxx2']";

The thing is that my post xxx2 are several values that I want inserted on several rows with the values xxx in front...

Last N Rows Of The Query
SELECT expensive query ORDER BY field ASC;
It generates somewhere around 2.9m rows. I want the last 10:

SELECT expensive query ORDER BY field DESC LIMIT 10

But I want them the other way around. Sure, I can do that programatically, but for "application reasons" I want that done in the query, so what I want to do is along the grounds of

SELECT expensive query ORDER BY field LIMIT 10 OFFSET rows()-10
We're using MySQL 4.0. Is there a way to achieve the above without using a temporary table?

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.

SQL Query Not Outputing All Rows.
My query doesn't seem to output all 6 rows in the database only two queries and i'm unsure why. I do know it is the query as when i do
SELECT * FROM case_studies
It outputs all rows.
The query i am usign is:
select programs.program_title,
programs.id, case_studies.id,
case_studies.title, case_studies.author,
case_studies.timestamp from case_studies,
programs WHERE programs.id
= case_studies.id
Any ideas?

Query Help, Comparing Rows
Suppose I have the following data:

+----+----------+-----+-----+-----+-----+-----+-----+-----+
| Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
| 11 | 11:20:00 | F | T | T | T | F | F | F |
| 12 | 11:45:00 | F | T | T | T | F | F | F |
| 14 | 12:10:00 | F | T | T | T | F | F | F |
| 15 | 12:35:00 | F | T | T | T | T | T | F |
| 17 | 13:00:00 | F | T | T | T | T | T | T |
| 18 | 13:25:00 | F | T | T | T | T | T | T |
| 19 | 13:50:00 | F | T | T | T | T | T | T |
| 20 | 11:28:00 | F | T | T | T | T | T | F |
| 21 | 11:53:00 | F | T | T | T | T | T | F |
| 22 | 12:18:00 | F | T | T | T | T | T | F |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.

How Many Rows Were Affected By Query?
How do I retrieve the number of affected rows by an UPDATE query with SQL? The C API exposes mysql_affected_rows() but I can't find documentation of the SQL equivalent...

I'm trying to find if an UPDATE had an effect within a stored procedure, so I can do an INSERT if there's no row to update.

Doing it the other direction to generate an error to act on would be quite wasteful... one INSERT and millions of errors per day.

Eliminate Rows In A Query (without Many OR)
This is my query :

SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas

There must be a more efficeint way to find what i want than this :

AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598

Something like cas.id is not (list of values)...

Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out.
The query I'm running is too slow...can anyone tell me what I can do to
speed it up..if I can at all? I was wondering if because ZipListMatrix has
3.3 million rows that 8 seconds is all the faster it's going to be. Any
help is greatly appreciated! I have already "optimized" the tables.

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;'
. ' '

I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work)

$sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";

Query Pulls Out Multiple Rows Even Though Theres Only One
Ive got a query thats selecting info about a product from a table called items and joining on a table called itemimages to get its associated images. The product can have more than one image. If i run the query on an item with 2 images i get 2 results for one item.....when theres only one item.....it seems to duplicate the item for each of its images....

SQL
SELECT items.*, itemimages.* FROM items INNER JOIN itemimages ON (items.itemID = itemimages.itemID) WHERE categoryID = '$category' AND active = 'yes' LIMIT $start, $limit"

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")

Is there any way to do this in one SQL query?

How Do I Update Multiple Rows In One Query?
How do I update multiple rows in 1 query? I try not to do a loop of update queries.

Average Query With 2 Rows From Same Table
My table:
"answer"
answerID
answer(int)
questionID(int)
userID(int)

answer1 is questionID = 1
answer2 is questionID = 2
WHERE userID is the same for both answer1 and answer2

I want the average of answer1/answer2: AVG(ans1/ans2), but how?

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Help With Updating Duplicate Rows In Mysql Query.
Quote:

SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC

What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group.

How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution.

I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest.

Also, if possible, I would like it to be done with one query.

Query To Display The Rows As Colums Is Not Working!!
I want a select query to get two columns. but i need to get these columns one below the other that is consider that i have a table student in that i have 2 columns name mark.
Now i want the result as

Name Raj Rina Tina
Marks 80 90 70

I tried the --vertical option of mysql and G option in the query.

for example when i tried

select *from studentsG

it displayed

*********** 1. row ***********
name: Raj
marks : 80
********** 2. row ************
name: Rina
marks : 90
********** 3. row ************
name: Tina
marks : 70

but i want to display

Name Raj Rina Tina
Marks 80 90 70

Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's.

$sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )";
return $sql2;
$res = mysql_query($sql2, $connDB) or die(mysql_error());
$row = mysql_fetch_assoc($res);
based on a test zip code gives a result like

SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 )
the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table.

In my db table lon and lat are decimal(10,6) type with a default value of 0.000000