MySQL Index question...

Ok, i've been doing LOTS of reading up on MySQL, im fairly new and just starting learning. Can anyone tell me what an index is? Or give me a link that explains it simply? I have google'd it, and have found nothing of any use really...

Can anyone point me in the right direction? :)

MySQL index question

how do i set up a constraint where given two rows: 1 1 ok 1 0 ok 2 2 ok 1 2 ok 1 1 wrong:constraint picks up error here because it has already seen that pair. never allows same pair of data twice. question is how do i set up this constraint. can't seem to find the awnser on the internet. have found ...

mysql index question

I am fighting with lock timeout problem on mysql right now. The front-end code is in Ruby on Rails, which updates some tables. The storage engine is innodb. In mysql log, I see things like the following Transaction 1 ... process no 3353, OS thread id 1094527296 inserting INSERT INTO ... (caused by c ...

MySQL Index Question

There's a query that i will be running a lot: $query = "SELECT foodid, CONCAT(food, ' (', brand, ')') AS xfood, ftype, sa, ss, FORMAT(cal, 0) AS xcal, "; $query = $query . "FORMAT(fat, 0) AS xfat, FORMAT(sat, 0) AS xsat, FORMAT(trans, 0) AS xtrans, FORMAT(chol, 0) AS xchol, FORMAT(sod ...

MySQL Double and multiple index question

I understand the benefits of using double key like KEY ('age', 'customer') for when you are using both in your where clause or sorting, etc.... I'm working with a pre-existing database. Some tables have several columns and only one key. In these cases, the key holds ALL colums ---- > KEY ('custom ...

MySQL Basic index question

I've a geolocation-table (MyIsam) with three rows: id (int), lon (int), lat (int). There are two different types of queries: ... WHERE lon=? AND lat=? ... WHERE id=? Which index-model has the best performance? A - single index over all 3 rows B - index (id) and index (lon,lat) C - separate indices f ...

MySQL Newbie question-Question about primary and index

Right now I have a member mysql database set up with a php login page. I am using MyPHPAdmin to do this. A member has to log in using their User_id number ( 4, 5 ,6 etc) I would like to make it so that they can log in using their email instead. 1-Do I edit the INDEX so that I make the primary the Em ...

MySQL Circular index question

Is this possible, and if so how can it be done in mysql. Let's say you have a table with an auto increment id 1,2,3,4,5,6,7,8,9,10 I would like to return the next 3 items, which could be done with an ORDER BY, until you get to 8. I need 8 to return 9,10,1 in that order. Hope this is possible in a si ...

mysql unique index question

I have a table CREATE TABLE processes ( process_id int primary key, process_name tinytext, process_type varchar(10), gallery_id int ); The `process_type`field has two possible values: - Task1 - Task2 Only one instance of "Task2" can run on a gallery at a time. Task1 cannot run on a gallery ...

MySQL Quick help with an index question?

my table looks like the following: id int(11) NO PRI NULL auto_increment username varchar(40) YES MUL NULL ratio float(4,2) YES NULL downloaded float(8,2) YES NULL downType varchar(2) YES NULL uploaded float(8,2) YES NULL upType varchar(2) YES NULL buffer float(8,2) YES NULL buffType varchar(2) YES ...

simple question on MySQL Index configuration...

This should be a fairly simple question to answer. I have a "user" table, with three columns that i always query seperately (i.e. user id, user email, and username). The user id is the designated as a primary key, the username and email are designated as unique. so my question is, to optim ...

Mysql index performance question

I have:WHERE l.uid = u.uidAND l.cid = ' . $cid . 'AND l.is_approved = 1which index combination is best:(cid-uid-is_approved) : 1 indexor(cid-is_approved and uid) : 2 indexesOr are thay the same?does someone know how mysql combines indexes?thx ...

MySQL Simple Index Question?

Q: tables t has 5000 records , and it's "flag" field is integer, flag=1 has records 2500, flag=0 has records 2500; I have build an index of "flag" but the problem is when i excute: EXPLAIN EXTENDED select * from t where flag=1 limit 10 explain result:rows >2000 I want to make ...

Ubuntu SQL Questions - Index question and trigger/stored proc question (MySQL)

General question about mysql, it's very important this runs as fast as possible. I'm fine with general suggestions, but when Replying please state how confident you are in your answer.Suppose I have this schema (ignoring indexes for now)-- pk# are all primary keys-- fk# is a foreign key on pk#-- map ...

jQuery currrently selected tab index question

I was trying to use the Tab select option to save the current tabindex to a cookie.It is my understanding that the select option executes a function whena tab is clicked. In this function I was trying to get currentlyselected using the .data("selected.tabs"); Here are my codes:jQuery(docum ...

Jquery index question

I have a HTML list:<ul class="buttons"><li><a href="#" class="active">Home</a></li><li><a href="#">About</a></li><li><a href="#" class="active">News</a></li></ ...

jQuery DatePicker mysql connectivity question

Hi,I am glad jquery has a forum of it's own I just found out!!!Like the subject says, I am wondering if making a mysql or sqlite database act as a backend for the date picker. I am trying to make an application just to learn jquery and php and I would like to have a calender that authenticated users ...

jQuery and mySql index don't match

I have a problem with index-ing. In jQuery it's zero based, in mySql it starts with 1. It's probably impossible to make mysql index from 0, so I have to fix it in the jQuery code, which folllows:$(document).ready(function(){          $("#tekst a").click(function() ...

PHP/MYSQL newbie question

here is my code:// user_profile is a table// password is a field in user_profile$selectStmt = "select password from user_profile WHERE userid=$form_userid";if (!($mailpwd = mysql_query($selectStmt, $link))) {DisplayErrMsg(sprintf("Error in executing %s stmt", $selectStmt));Displa ...

MySQL Simple question on linking to a database using ODBC

Newbie question: I want to link to table versus doing an import of that table. Is there a way to do this? I find that I can only do an import of data using the Import/Export Wizard. ...

MySQL database question

I use Open Office & need a database. I don't necessarily need a big one. I have been using MS Works databases, but it won't run under XP even in compatible mode so I have been using spreadsheets & saving to db which truncates data. I need a few suggestions about which one is best to download ...

MySQL grouping question

everyone, this is my first post here. I maintain a blog, and I would like to list the latest 3 blogs commented on. I am having trouble coming up with a query that effectively searches by date DESC and groups by BLOGID. Say I had a few rows like this: id=1 date=11-27 blogid=7 id=2 date=11-28 blogid=1 ...

MySQL Index not updating

I've created a table with several indexes as follows: $query = "CREATE TABLE `data_raw` ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id VARCHAR(20) DEFAULT 'error_internal' NOT NULL, time_run DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL, ...clip… PRIMARY KEY (id), INDEX x_run_id (run_ ...

MySQL Subqueryes question

I want to select some values from one table, where some value is equal to some data selected from table from DIFFERENT database. how to point to this different database? ...

General Index Question

I am playing with some of our older SQL statements and trying to tweak them but little confused by the usage of index. Sometime it uses the index sometime it does not even though columns which are part of the index are part of the sql. So, my question is there are set rule when index will be used an ...

MySQL lock question

I am a new mysql user. This is a question for the classic edition of mysql. I know that in the pro edition the InnoDB has a row-level locks and I wanted to know if there is any protection ( lock or something like that ) in the classic edition as well. I want to know this beacuse I want to know if 2 ...

MySQL newbie question re: user login

all, I'm a little confused with the following concep, and it's hard to explain. user logs in to the database via a website, and sees user-specific content, such as his profile, no one else's. That's fine, but what I don't understand is: user/pass/login checks are made against the inherant MYSQL data ...

MySQL subquery question.

I've tried to do this query. ============= qry starts ======================================== SELECT SDATE, SSTONUM, SUM(SQTY ) FROM ( SELECT SUBSTR(DATE,-5) AS SDATE, STONUM AS SSTONUM, QTY AS SQTY FROM TTAB ) AS TT GROUP BY SDATE, SSTONUM ============= qry ends =================================== ...

MySQL Query Question

I have two tables, customer table and insurances table. Customer table: customer autoincrement (primary key) name varchar .... Insurance table: company varchar(6) insurance carchar(13) customer integer .... i need to filter the customers that add insurances only in company 'axa' ist's possible? ...

MySQL Maths Question

I want to retrieve data from a MySQL database through a PHP web page. Now the problem is I’ll need to do some maths on the data (like plus, minus, divide etc.). Would I need the SQL Query to do the maths or should the maths be done in the PHP coding before it's displayed? ...