Hey All Yet another question by me.. I have a database with about 2.5 million entries on. I orginally had .. SELECT * FROM data WHERE xxx = xxx then changed it to SELECT col1, col2 FROM data WHERE xxx = xxx but it still takes a while to display any results. I have using a php page on a website to show the results. Any ideas has to speed this up ? Alex
The lack of speed is going to be in the WHERE part of the query. Make sure you have indexes on the columns in WHERE command. Also things like WHERE `column` LIKE '%%' is extremely slow.
Here's some info on indexes. http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Generally good indexing (or lack thereof) will make or break a database's usability. Can you post the Where clause and the column data types (int, varchar, etc) of the columns in the where clause from the above query.
Indexes are created for searching databases quickly, which is exactly what you are trying to do. The frequently searched on fields should have indexes on them or your query could take a much longer time to run.
Assuming you have your indexes and your queries are still slow you might want to look at the explain or analyze funtionality of your Database server. Most database server allow you to analyze how your query is parsed and executed. Since you have not stated which Database server you are using I can't tell you the specifics. Another problem could be in the amount of data transferred between client and server. You have already limited the column to those you actually need. The second variable is the number of resultset records that need to be transferred between client and server, are you requesting all the records (thousands) when you only need ten. Most Database servers have functionality to limit with for example LIMIT and OFFSET keywords.
Hi Thanks for your help. I have been reading up on this indexing, but dont know how to impletement it. The dababase/table i have is of all the postcodes and their grid co-ordinates. So each row is unique. Also the database is in order of postcodes alphabetically. I read that indexing is good if the results are not in order or you want to run different selects. I just basically just want to select the GridE and GridN where postcode = xxx xxx. If indexing is still needed, can anyone show me how?
If the postcodes column is unique, then try this: CREATE UNIQUE INDEX postcodes_index ON postcodes; If there are any duplicates use this: CREATE INDEX postcodes_index ON postcodes; Either way, this should speed up the query a lot. If it doesn't, you can always delete the index. Also, make sure you have a good backup of your database before you start messing around with the existing structure. Not likely for an error, but it does happen occasionally. What data type is the postcodes field? Any type of char field would be a lot slower than an int field, so if the data allows for it, you may want to convert it to an int [unsigned].
Hm if the whole select is based on only one table and after you have created those indexes, there shouldn't be left many reasons for slow processing.. If there are more than 1 table in the select and they are joined in the where clause, that would be a really good "excuse" for some crippling performace.
Also the database is in order of postcodes alphabetically. I read that indexing is good if the results are not in order or you want to run different selects. Indexing works best when the where clause selects only a part of the total records in the table. The index can then be used as a quick lookup table to find the datablocks containing the needed records. Since the index needs to be updated when the table is changed, it has a slight performance penalty. The usual quote is to only need as much Indexes as you really need, no more. The Index CAN be used by the Database server when it "thinks" that it has performance advantages to do so, which is most of the times. Sometimes the Database server can find the data faster by doing a full table scan, which is something your doing right now. P.S. I know the Postgresql has a way to optimize a table for one specific query, which should be done at interval by reordering the on disk structure, which is an expensive operation. But which Database server are you using to get official guarantees about on disk structure and on disk ordering ? Sounds like something along the lines of Oracle.