Is this possible?

[Andy-H]

Basically, I have a table with over 20k rows, when I query it, despite the results being paginated to 20 per result set, the query takes around a second to run, I want to know if its possible to create a MySQL view, with the keys of one table and a limit field which will always remain in natural order and in increments of 1. This way I can join the tables and do:

 

 

SELECT p.* FROM view v INNER JOIN premises p USING ( id ) WHERE v.incremental_field >= 0 AND v.incremental_field < 20 ORDER BY v.id ASC

 

 

Anyone know if this is possible? Whether it would improve performance or whether there is a better alternative?

 

 

My current query is

SELECT CONCAT(p.id, ':', p.branch_name) AS branch_name, p.addr, p.postcode, CONCAT(c.first_name, ' ', c.last_name) AS contact, c1.info AS mob, c2.info AS email, p.disabled
                                          FROM premises p LEFT JOIN ( contacts c ) ON ( c.prem_id = p.id )
                                          LEFT JOIN ( contact_info c1 ) ON ( c1.contact_id = c.id AND c1.type_id = (SELECT id FROM types WHERE type = 'Mobile' ) )
                                          LEFT JOIN ( contact_info c2 ) ON ( c2.contact_id = c.id AND c2.type_id = (SELECT id FROM types WHERE type = 'Email'  ) )
                                          ". $append ." ORDER BY p.id ASC LIMIT $low, 20

[fenway]

Show us EXPLAIN output.

[Andy-H]

 

 

id[/color]select_type[/color]table[/color]type[/color]possible_keys[/color]key[/color]key_len[/color]ref[/color]rows[/color]Extra 1[/t]PRIMARYpindexNULLPRIMARY4NULL20 1PRIMARYcrefprem_idprem_id4phantom.p.id1 1PRIMARYc1refcontact_idcontact_id8phantom.c.id,const1 1PRIMARYc2refcontact_idcontact_id8phantom.c.id,const1 [/color]3SUBQUERYtypesALLNULLNULLNULLNULL4Using where 2SUBQUERYtypesALLNULLNULLNULLNULL4Using where I don't get any of that lol

[Andy-H]

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY p index NULL PRIMARY 4 NULL 20

1 PRIMARY c ref prem_id prem_id 4 phantom.p.id 1

1 PRIMARY c1 ref contact_id contact_id 8 phantom.c.id,const 1

1 PRIMARY c2 ref contact_id contact_id 8 phantom.c.id,const 1

3 SUBQUERY types ALL NULL NULL NULL NULL 4 Using where

2 SUBQUERY types ALL NULL NULL NULL NULL 4 Using where

 

 

I don't get any of that lol

[fenway]

That shouldn't take a full second.