1

i need hand to index a large table ! and i have no idea about index mysql tables

this is the query when i order rows from table

                     SELECT  "posts.* AS `posts` , user.nickname AS nickname
                      FROM `posts`
                       LEFT JOIN user AS user ON (user.userid = posts.userid )
                       WHERE
                        posts.userid= '" . intval($bbinfo['userid']) . "'
                       ORDER BY posts.timestamp DESC
                       LIMIT $start , $_limit
                      "

how i can use index to index this table after inser a new post to the table ? or by alert the table where and when i can use index table and how ? please help

Improve this question

3 Answers 3

Reset to default
2

Just create the index and define the way it works. Then you have nothing to do. If the SQL storage engine think your index should be used he will use it. And when you create or update data it will be maintained.

Now the hard part is the definition of the index.

You can see an index as an order, like when you use a phone book. Your phone book is ordered by city, then by lastName and then by first name. It's an oreder stored near the table that the engine can use to find the results faster than it would be if he needs to read the whole table data.

In a phone book there is only one index, so the data is ordered on, that index. In a database you can have several indexes, so they are stored near the table and contains pointers to the real data addresses.

Indexes are very important when you search data. You can easily find people names Smith in New York. It's harder to find all the Smith in all US cities (with a phone book).

In your query you have two instructions that may benefits from an index. You are filtering by user and then ordering by timestamp.

If you create an index by user and then timestamp the engine will already have the solution of your query by simply reading the index.

So I would create this one:

CREATE index posts_user_and_timestamp_idx ON posts(userid, timestamp DESC);

And this index could be reused for all queries where you are simply filtering by users (like the phone book. You can easily extract pages about one city). But not for queries where the only filter is the timestamp (you would need an index on the timestamp only, hard to extract all smith on all cities from the phone book).

So in fact the main problem of index is that they heavily depends on the queries you are usually using on the database. If you are never using the same sort of queries on a table then you will need a lot of different indexes. And an index is something which takes a looot of place. Most tables are using 3 or 4 more physical space for indexes than for the data.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

thanks but how i can make sure the table has indexed ? i am using php to query the mysql , i can't use ssh because i am running the server on my laptop by local server
If you don't have direct access to your database (to run commands), do you have a DBA or someone else who does?
@Jack K Fouani: use database administration tools to create and maintain indexes, like phpmyadmin. Or do it in your PHP code. In fact do it in the same place you have created the tables. An index is an object of the database, like tables, columns, views, triggers, etc.
yea i have direct access i am runing local server on my laptop , i can use phpmyadmin , but i can't find something like index ! , so i am seeking simple way to index my tables
|
2

You should find a MySQL admin tool that works for you since schema changes to your dbs, including adding indexes are a very common task.

I use MySQL Workbench to do most of the schema manipulation, including setting indexes on tables. This is a free admin app for mySQL dbs. If you dont have it, download it.

http://dev.mysql.com/downloads/workbench/5.1.html

Open your db in Workbench, right click on the table to add the index to and choose Alter Table... Then click on indexes at the bottom of the window, you should see something similar to:

enter image description here

You can also use PHPMyAdmin, which is a little more complex and a little harder to instal, IMHO.

I drilled down into my Program Files directory (Windows XP) to find the PHPMyAdmin executable file - which launched the app.

From PHPMyAdmin 3.2.1 - open your schema. Click on the table - which presents you with a GUI menu that will allow you to easily specify an index using the icon with the lighting bolt to the right of the column to be indexed. enter image description here

Improve this answer

3 Comments

but wait , after i index the table , can i use normal query with no additional things like EXPLAIN select * FROM blaa , or just use directly SELECT * FROM blaa ?
setting index will not effect how you type your SQL.
@jack, if this answer was acceptable, remember to select it as such.
1

You only need to add an index once. No need to worry about doing anything after every INSERT. Based-on what you have in your post, I would try something like this:

CREATE INDEX posts_userid_idx ON posts(userid);

If that doesn't seem to work very well, I would then advise you to check the MySQL Documentation on CREATE INDEX and see if any of the available options would apply to your situation.

Based-on your (revised) comment, you should also add a PRIMARY KEY on postid, as well.

ALTER TABLE posts ADD PRIMARY KEY (postid);

And yes, you should be able to run both of those commands in MySQL Workbench as you would any other query.

Improve this answer

6 Comments

hi i have this on my table postid int(11) NOT NULL AUTO_INCREMENT, userid int(11) NOT NULL, posteruserid int(11) NOT NULL, text text NOT NULL, timestamp int(11) NOT NULL, position char(55) NOT NULL, poster char(55) NOT NULL, mhash varchar(55) NOT NULL, PRIMARY KEY (postid), KEY wallerid (wallerid), KEY wallerid_2 (wallerid) and how to run your query ? by using mysql query ?
No problem, @JackKFouani. Since you are new to this site, I just wanted to add that the best way to say "thank you" for an answer, is to "accept" it or to give it a +1. Thanks.
how i can run to know if table is indexed ? i am using php and mysql , i was try to run the query above and using EXPLAIN but its show only 2 empty rows lol , i made alot of research about index , but its hard to understand , so can you give me the query how to know if table indexed or not with php and mysql , i can't use ssh
@Jack F Fouani: use SHOW CREATE TABLE posts; and you will see your indexes
hi please check this picture , is the table indexed right ? m5zn.com/uploads2/2011/12/25/photo/…
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.