increase speed of database by creating smaller table.

[hamdi123]

Okay hello everyone.

i have two question

Question 1

-----------

i need help with sql code to increase speed of database by creating smaller table.

i have a database with two tables each table with over 100,000 rows

1. ques

2. answers

i need to select from these big table and insert to another smaller table that is categorized.

i know i can select by categories using

SELECT * FROM ques WHERE category = 'some category'

but how can i select and insert the result to another table?

 

Question 2

----------

after above code completed i need to select from table 'answers' that matches table 'ques' index ID

compare table ques and column queid to table 'answers' and column 'queid'

something like this - but working version of it

 

SELECT * FROM answers where ques.queid = answers.queid

then insert the result of table 'answers' to another smaller table that i created

[crtreedude]

Let's step back a bit, 100,000 records is hardly anything. I have worked on a trillion record database.

 

If it is working slow, it is due to not enough memory or no index on what you are searching. You should fix that first. You should be in 40 million records and above before you have to get good at tuning.

 

Indexing is a way that you create a smaller table when you think of it.

[fenway]

Some EXPLAIN output, perhaps?

[hamdi123]

Some EXPLAIN output, perhaps?

 

thanks for the first answer it did explain a lot. for the second answer the out put you could understand if you see the page located http://www.askgetanswer.com

[fenway]

EXPLAIN is a mysql modifier to SELECT; perhaps you should look at the sticky on posting questions if you're not sure how.

[hamdi123]

EXPLAIN is a mysql modifier to SELECT; perhaps you should look at the sticky on posting questions if you're not sure how.

 

wow didnt know that one coming. so you saying use EXPLAIN to speed the database load time

[hamdi123]

EXPLAIN is a mysql modifier to SELECT; perhaps you should look at the sticky on posting questions if you're not sure how.

 

ohh i just find out what is EXPLAIN is - i will try use and post the result here - thanks