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? :)

Smilar Topics

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

  • 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() ...

  • MySQL username question

    I just installed MySQL on my Mac with OS X 10.3, Apache and PHP. I thought installing and getting started with MySQL would be tricky like a lot of developer stuff on unix machines with builds and everything. It was easy!! And the online tutorial/reference got me going in minutes. Really great! Thank ...

  • MySQL trigger question

    We have to 2 web applications running in 2 different server and mysql. My question is it possible to use trigger to sync a table in the 2 database running in different server?Edited 2 time(s). Last edit at 07/17/2008 01:46AM by jack mapa. ...

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

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

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

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

  • Sphinx Delta Index Question

    In regards to my previous question - I now have everything operating correctly. However,I would like to go the main+delta route to save having to reindex the entire site everynight. How would I go about setting up a delta index and then merging the two together?Here is my current sphinx.conf<!-- ...

  • MySQL Index for a large myisam table (MYD ~=7GB, MYI ~= 7GB)

    I'm posting my question again in case gurus missed my first one :-) I have a very large table with 66,836,760 rows (in my world), MYI and MYD files each about 7GB in sizes and even though I've created indexes for all the possible columns, some queries are still slow. I think I read somewhere that fo ...

  • MySQL Index vs. two-columned orthogonal table

    I am currently optimizing some tables for faster select-queries (inserts are not critical) and now I came to the following question. I am having a table keeping an archive of tracklist entries of several webradio stations. It's performing quite bad since it had been done just for fun in a code-and-f ...

  • MySQL ENUM question

    Ok, so in the MySQL documentation section 7.3.4.3 it reads"If you store a number into an ENUM, the number is treated as an index, and the value stored is the enumeration member with that index. (However, this will not work with LOAD DATA, which treats all input as strings.)"Now my question ...

  • MySQL index optimizing

    A question for MySQL gurus:I'm going to build a rather big database (local search engine). There will be sIndex table, that contains id references to other tables (words, documents, etc.). As I see, it tends to grow dramatically: as for now, I have total size of indexed documents about 1M, and the s ...

  • mysql index re-order problem

    need your help I new to php and mysql, I have a simple mysql database that accepts name phone number, my problem is when i delete a record. something like below:---------------------------index name phone1 john 12345672 mike 43011113 jenny 51123784 sonny 8331799-------------------------------when I ...

  • MySQL index for datawarehouse

    I'm new to this community.. I have a question about the use of MySQL as data warehouse. I have deployed a simple DW solution using MySQL database...Now I must define the indices. As I alway studied that the best index for DW are the Bitmap ones, which are not supported in MySQL. What could be an alt ...

  • MySQL Index

    I'm looking at my phpmyadmin status page and Handler_read_rnd_next is in red "300M" I was reading it says "Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have. " Here is my code below $tn ...

  • MySQL Basic question regarding optimize table

    We are using MySQL community edition version 5.0.77 included with RHEL 5.5. I hope this post/question/train wreck will be interesting to at least some of you out there and there is a solution (viable) for what we have encountered. I'm trying to deal with an issue caused by poor design and compounded ...

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

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

  • update mysql index

    I have a noob doubt about indexing columns in MySQL.Once the index is generated, doesn't it need to be updated from time to time?If so, how?would a query likeALTER TABLE `jos_content` UPDATE INDEX `state_access` ( `state` , `access` , `publish_up` , `publish_down` , `id` , `catid` , `sectionid` , `c ...